发表 xbdba 12c新特性8 分钟读完 (大约1129个字)

Identity Columns

标识列是12c的一个新特性，能自动生成列里的值，一部分功能与mysql中的自增列有点类似，简化了应用程序的开发和其他关系型数据库迁移到oracle。这个特性主要是利用一个默认的序列生成器，通过这个生成器来绝对字段增加或者减少的整数值。

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table t1 purge;
create table t1
(id number generated by default as identity,
name varchar2(10));

insert into t1 (name) values ('xb');
commit;

select * from t1;

        ID NAME
---------- ----------
         1 xb

对于标识列来说，我们要么指定一个默认的序列去生成字段值或者只对那些插入NULL的情况生成字段值。根据标识列的定义可以有GENERATED ALWAYS和GENERATED BY DEFAULT关键字。

GENERATED ALWAYS

oracle使用序列生成器来确定列值，但也可以显示的指定某个值。如果指定了ON NULL关键字那么序列生成器会在你插入NULL值的时候生成一个值给字段。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
drop table t1 purge;
create table t1
(id number generated by default as identity,
name varchar2(10));

insert into t1 (name) values ('xb');
commit;

xb@PDB12C> insert into t1 values(null,'xb2');
insert into t1 values(null,'xb2')
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XB"."T1"."ID")

select * from t1;

        ID NAME
---------- ----------
         1 xb

xb@PDB12C> @desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                              NOT NULL NUMBER
    2      NAME                                     VARCHAR2(10)

可以看到通过序列生成器生成的字段在创建的时候默认就是NOT NULL属性，因为这里没有对NULL的情况进行指定，所以序列生成器在碰到NULL的情况无法生成值，则会报错

加上ON NULL关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t1 purge;
create table t1
(id number generated by default on NULL as identity,
name varchar2(10));

insert into t1 values(1,'xb');
insert into t1 values(9,'xb2');
insert into t1 values(null,'xb3');
commit;

xb@PDB12C> select * from t1;

        ID NAME
---------- ----------
         1 xb
         9 xb2
         1 xb3

这样当插入的字段值为NULL时，生成器则会自动生成一个整数值给字段。通过执行计划也可以看到，在对有序列生成器属性的表插入数据时，其实是访问的序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
xb@PDB12C> insert into t1 values(null,'xb3');

1 row created.

xb@PDB12C> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4b1kmg40ra83t, child number 0
-------------------------------------
insert into t1 values(null,'xb3')

Plan hash value: 948190082

---------------------------------------------------------
| Id  | Operation                | Name         | Cost  |
---------------------------------------------------------
|   0 | INSERT STATEMENT         |              |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | T1           |       |
|   2 |   SEQUENCE               | ISEQ$$_90071 |       |
---------------------------------------------------------

   - cpu costing is off (consider enabling it)


16 rows selected.

# 而这个序列也是真实存在的，说明是用户自动创建的
xb@PDB12C> select * from user_sequences;

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S K
-------------------- ---------- ---------- ------------ - - ---------- ----------- - - - -
ISEQ$$_90071                  1 1.0000E+28            1 N N         20          21 N N N N

Generated Always

字段无法被用户所更新，所以序列生成器生成的值是列唯一可能的值，如果像之前一样指定某个值或修改都会报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
drop table t2 purge;
create table t2
(id number generated always as identity,
name varchar2(10));

xb@PDB12C> insert into t2 values(1,'xb');
insert into t2 values(1,'xb')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

xb@PDB12C> insert into t2 values(null,'xb');
insert into t2 values(null,'xb')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

insert into t2(name) values('xb');
insert into t2(name) values('xb2');
insert into t2(name) values('xb3');
commit;

xb@PDB12C> select * from t2;

        ID NAME
---------- ----------
         1 xb
         2 xb2
         3 xb3

xb@PDB12C> update t2 set id=4 where name='xb';
update t2 set id=4 where name='xb'
              *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column

由于是使用的序列，那么也可以用到一些序列中的特性，可以指定不同的初始值和累加值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t3 purge;
create table t3
(id number generated always as identity (start with 2 increment by 2),
name varchar2(10));

insert into t3(name) values('xb');
insert into t3(name) values('xb2');
insert into t3(name) values('xb3');
commit;

xb@PDB12C> select * from t3;

        ID NAME
---------- ----------
         2 xb
         4 xb2
         6 xb3

通过USER_TAB_IDENTITY_COLS等视图可以查询到一些标识列的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set linesize 300
col table_name for a10
col COLUMN_NAME for a10
col IDENTITY_OPTIONS for a70
select * from USER_TAB_IDENTITY_COLS;

TABLE_NAME COLUMN_NAM GENERATION SEQUENCE_NAME        IDENTITY_OPTIONS
---------- ---------- ---------- -------------------- ----------------------------------------------------------------------
T3         ID         ALWAYS     ISEQ$$_90075         START WITH: 2, INCREMENT BY: 2, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

T1         ID         BY DEFAULT ISEQ$$_90079         START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

T2         ID         ALWAYS     ISEQ$$_90073         START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

这个特性目前也有一些限制条件：

  • 每张表只能由一个标识列
  • 标识列的数据类型必须是数字型，而且不能是用户自定义的类型
  • 标识列中不能有DEFAULT关键字
  • 标识列中，NOT NULL和NON DEFERRABLE约束都是隐式存在
  • CTAS语句无法继承字段的标识属性
发表 xbdba Dataguard17 分钟读完 (大约2493个字)

Dataguard中pdb的迁移和故障切换

在12c多租户环境中，每一个CDB当中都会有多个pdb在运行，在某些情况下会涉及到CDB之间进行pdb的迁移。当然也就会碰到类似的情况，比如只有其中某一个pdb出现了问题，而其他的pdb都运行正常，这个时候如果对CDB做failover代价太大，这时为了最大程度减少停机时间和缩小影响，我们可以只对这个单独的pdb进行failover或者迁移到同服务器的其他primary cdb。这就需要用到dg broker所带来的新功能，从12.1版本开始，通过dg broker中migrate pluggable database命令能很方便的对pdb进行迁移操作，方便的同时得需要满足一些条件才可以实现，数据库的版本、补丁、COMPATIBLE参数等

实验环境大致如下

Host CDBNAME PDBNAME
Pri ORA12C PDB12C
Pri ORA12C PDB3
Stb ORA12C PDB12C
Stb ORA12C PDB3
Stb CDB1 PDB1

注意事项

下面的步骤假设源CDB库（无论是主库还是备库用来做迁移和failover）和目标CDB库能访问到同一个存储，这样就不需要复制文件操作。

  • 源CDB库可以是RAC也可以是单实例
  • 源CDB的备库不需要是ADG，尽管备库的CDB和PDB都需要被打开为只读模式一段时间
  • 目标CDB可以是RAC也可以是单实例
  • 源端和目标端的存储方式可以是文件系统也可以是ASM
  • 如果是从备库进行迁移，确保之前已经建好了TEMP临时文件
  • 如果源PDB正常关闭：
    • 对于迁移操作，源pdb必须要打开，而且恢复到当前状态否则迁移的过程中会拔下失败
    • 对于failover，源端pdb的正常关闭没什么影响。拔下操作会失败，需要手动清理并完成操作。
    • 如果目标CDB的版本较高，则pdb仍能插入但是处于关闭状态，需要手动进行升级。
    • 无法迁移或者failover到低版本数据库
    • 迁移和failover不支持PDB快照克隆
    • 任务执行完毕以后，需要手动清理主库剩余的数据文件
    • 目标库插入pdb时是通过STANDBYS=NONE的方式，所以在目标库的其他备库中需要手动启用恢复

迁移过程

迁移是用来将PDB从一个cdb迁移到其他cdb中去，migrate命令会将dgbroker配置中主库的pdb拔下，然后插入到另外一个configuration中的主库当中去。

可以将pdb迁移到更高版本的CDB当中去，往低版本则不行。在迁往更高的版本以后，被迁移的pdb在目标cdb中处于关闭状态，你必须执行一些操作来让pdb进行升级。同样pdb可以迁移到更高COMPATIBLE参数值的数据库当中去。

迁移过程的大致步骤如下：

  1. 停止所有连到被迁移pdb的服务
  2. 如果dg环境中你却没有打补丁Patch 25616359 的话，则必须先要停掉备库上所有的pdb，否则redo apply会失败
  3. dgmgrl连接到主库
1
2
$ dgmgrl 
DGMGRL> connect sys as sysdba
  1. 执行migrate命令 
1
2
3
4
5
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<DEST_SID>/<PDBNAME1.xml>' connect as <USERNAME>@"<dest-cdb-connect-identifer>";  

-or-

DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<DEST_SID>/<PDBNAME1.xml>':

manifest文件存放路径必须可以同时被源端主库和目标端主库访问的到

  1. 当连上目标库以后，migrate命令会做以下事情：
  • 对migrate操作做一些必要的验证工作
  • 从主库拔下pdb
  • 在目标库上使用主库的数据文件通过STANDBYS=NONE的方式创建PDB
  • 如果目标库的版本与源端一样，则在其所有实例中打开PDB
  • 在源端主库中删除pdb，同时删除源端备库中的pdb
  1. 当migrate命令完成以后则执行以下任务：
  • 将之前连接到pdb的服务进行重连
  • 将目标端CDB中的pdb进行备份方便以后恢复
  • 参照Note 1916648.1中开启pdb的备库同步

迁移测试用例

主库pdb的状态

1
2
3
4
5
6
7
8
9
10
11
12
13
sys@ORA12C> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ WRITE NO
         4 PDB3                           READ WRITE NO

sys@ORA12C> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

生成pdb的xml文件

1
2
3
4
5
6
7
8
9
10
11
sys@ORA12C> alter session set container=pdb3;

Session altered.

BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/u01/app/oracle/oradata/pdb3.xml');
END;
/

PL/SQL procedure successfully completed.

在备库机器上连接到源库的dg broker

1
2
3
DGMGRL> connect sys/oracle@ora12c_dg
Connected to "ORA12C_DG"
Connected as SYSDBA.

然后执行迁移命令，使用之前生成的xml文件，因为这里只能使用本地路径，所以必须要是共享目录或者同一个机器上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
DGMGRL> migrate pluggable database immediate pdb3 to container cdb1 using '/u01/app/oracle/oradata/pdb3.xml';
Username: sys@cdb1
Password:
Connected to "cdb1"
Connected as SYSDBA.

Beginning migration of pluggable database PDB3.
Source multitenant container database is ORA12C_DG.
Destination multitenant container database is cdb1.

Connected to "ora12c"
Closing pluggable database PDB3 on all instances of multitenant container database ora12c.
Pluggable database PDB3 on database ORA12C_DG lags its primary
database, ora12c, by 2129 seconds.
Continuing with migration of pluggable database PDB3 to multitenant container database cdb1.
Stopping Redo Apply services on source multitenant container database ORA12C_DG.
Pluggable database description will be written to /u01/app/oracle/oradata/pdb3.xml.
Closing pluggable database PDB3 on all instances of multitenant container database ORA12C_DG.
Disabling media recovery for pluggable database PDB3.
Restarting redo apply services on source multitenant container database ORA12C_DG.
Creating pluggable database PDB3 on multitenant container database cdb1.
Opening pluggable database PDB3 on all instances of multitenant container database cdb1.
Unplugging pluggable database PDB3 from multitenant container database ora12c.
Pluggable database description will be written to /tmp/ora_tfils2PLmU.xml.
Dropping pluggable database PDB3 from multitenant container database ora12c.
Migration of pluggable database PDB3 completed.
Succeeded.

-- 检查目标端cdb的pdb状态
sys@CDB1> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB3                           READ WRITE NO

-- 日志能看到并没有文件的拷贝动作
Completed: create pluggable database PDB3 using '/u01/app/oracle/oradata/pdb3.xml' nocopy standbys=none tempfile reuse
alter pluggable database PDB3 open instances=all
PDB3(4):Autotune of undo retention is turned on.

failover步骤

下面的图展示了将一个故障的pdb迁移到新的cdb过程中初始和结束的状态。

CDB1有三个pdb（PDB1,PDB2,PDB3），同时有一个物理备库。在物理备库同一个机器上有另外一个CDB2,CDB2是可读写状态，出故障的pdb将会迁移到这个CDB上面来。

迁移前的状态：

目前CDB1上的所有pdb都运行正常，CDB1的备库不是处于active dg。CDB2有自己的pdb4

迁移后的状态：

PDB2出现故障，估算需要很长的时间才能恢复，但是目前PDB1和PDB3均允许正常，而且备库的同步也都正常。为了最低程度减小影响，将使用备库pdb2的数据文件去插入到CDB2当中作为读写状态提供给应用访问，然后删除CDB1上的pdb2。这不是一个本机拔插操作，因为它需要一个可读写的CDB。

过程

failover主要用于主库的某个pdb出现了故障，而其他pdbs都允许正常，这时就需要将这个pdb移到其他CDB上去。它提供了一个方法可以只对主库的其中一个pdb进行故障转移而不用影响其他库。备库必须要与目标CDB共享数据文件存放的目录和通过DBMS_PDB.DESCRIBE生成的XML文件所在路径，目标CDB都要能够访问到这些文件，无论是通过在同一台机器或者NFS挂载的方式。

Failover不是一个计划性操作，所以目标就是尽可能降低停机时间。所以要确保一些前提条件：

  • 源和目标库都必须打上相同的补丁包，这样就不用执行额外的脚本，这些内容会在DGMGRL CLI MIGRATE命令时用到，最好的方式是目标CDB与源备库使用同一个oracle home目录
  • 目标CDB最好与源库安装的同样的Oracle组件

failover步骤：

  1. 停掉所有连接到故障pdb主备库的服务
  2. 连接dgmgrl到备库，使用sysdba角色
1
2
$ dgmgrl 
DGMGRL> connect >USERNAME> as sysdba
  1. 在dgmgrl命令行中执行failover操作。Note：这里的操作与之前的迁移命令一模一样 
1
2
3
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<PDBNAME1>/<PDBNAME1.xml>' connect as <USERNAME>@"<dest-cdb-connect-identifer>";
-or-
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<PDBNAME1>/<PDBNAME1.xml>': # This will prompt for user name and password
  1. 当到目标CDB的连接建立以后，主要做了如下事情：
    1. 做了很多failover操作必要的校验
    2. 如果备库的redo apply运行则会停掉
    3. 将所有备库实例打开到read only状态
    4. 根据迁移命令指定的路径通过DBMS_PDB.DESCRIBE创建XML文件
    5. 停止源备库PDB的恢复
    6. 如果源备库本身不是ADG状态则还原成mount
    7. 通过备库的数据文件（NOCOPY）在目标CDB创建PDB，使用STANDBYS=NONE选项
    8. 在目标CDB的所有实例打开PDB
    9. 拔掉源主库的PDB，如果操作有报错则需要手动解决
    10. 如果拔下操作顺利，则通过KEEP DATAFILES的方式删掉PDB，这同样会删除所有源备库的pdb。
  2. 如果都顺利结束，则将之前的服务都连到新PDB上来

  3. 备份目标CDB的PDB允许恢复
  4. 开启目标CDB所有备库的恢复操作

因为Failover过程中使用的DGMGRL命令与之前迁移操作的基本一样，这里就不再一一赘述，全文完。

发表 xbdba Dataguard24 分钟读完 (大约3644个字)

12c rac to rac 物理备库最佳实践

这篇文章主要目的是详细记录在12cR2环境中，配置主备都是rac环境的dataguard，下图列出了本次将要创建的环境

准备步骤说明

  • 通过vmware创建虚拟机
  • 总共4台，两台作为主库，两台作为备库
  • 主库安装12cR2 rac环境，创建实例
  • 备库安装12cR2 grid软件和database软件，不创建实例

测试环境介绍

画图的时候手误，scan hostname两节点是一致的

主库：

备库：

所用软件

  • VMWARE 14 pro
  • Oracle Grid Infrastructure 12c R2
  • Oracle Database 12c R2
  • Oracle Linux 7.6

Dataguard 配置

  • Protection mode: Maximum Performance
  • Fast-start failover: Disabled
  • Standby database type: Physical Standby

Rac环境中dg注意事项

rac环境中配置dg与单机中的配置有所不同：

  • 在Rman中执行duplicate database for standby from active database命令时，只会在备库创建一个单实例，你需要手动设置rac参数包括INSTANCE_NUMBER和INSTANCE_NAME等，在另一个主机上启用第二个实例，然后添加新创建的数据库到OCR中
  • INSTANCE_NUMBER和INSTANCE_NAME参数必须在第二个实例当中手动修改
  • 不管主库或者备库都可以是单机的非集群环境
  • standby redo logfiles必须在共享磁盘上，每个备库实例必须配置归档standby redo log到同样目录
  • 如果没设置OMF,则LOG_ARCHIVE_FORMAT参数必须设置，需要加上%t或者T%来确保名称的唯一性

安装大纲

下面列出了安装的标准化步骤

这里默认主备库都已经安装好了所需环境，如果对12cR2 rac安装不清楚的可以参考我之前的文章：在vmware linux 7.5安装oracle 12c rac

准备主库环境

  • 开启归档
  • 配置ARCHIVELOG DELETION POLICY策略
  • 开启forced logging
  • 配置standby redo log files
  • 配置主库相关参数
  • 开启数据库闪回
  • 配置CONTROL_FILE_RECORD_KEEP_TIME参数
  • 配置tnsnames.ora

准备备库环境

  • 创建相关目录
  • 创建静态监听
  • 创建init.ora
  • 拷贝主库password文件
  • 配置tnsnames.ora

创建物理备库

  • 通过rman duplicate在线创建
  • 设置备库参数
  • 开启redo apply
  • 检查物理备库同步情况

检查环境

确保每个节点都能访问其他所有地址

主库

1
2
3
4
5
6
7
8
9
10
192.168.146.121  racnode1 racnode1.oracle.com
192.168.146.123  racnode1-vip

192.168.146.122  racnode2 racnode2.oracle.com
192.168.146.124  racnode2-vip

192.168.146.125  racnode-scan

10.10.10.121  racnode1-priv
10.10.10.122  racnode2-priv

备库

1
2
3
4
5
6
7
8
9
10
192.168.146.131  stbracnode1 stbracnode1.oracle.com
192.168.146.133  stbracnode1-vip

192.168.146.132  stbracnode2 stbracnode2.oracle.com
192.168.146.134  stbracnode2-vip

192.168.146.135  stbracnode-scan

10.10.10.131  stbracnode1-priv
10.10.10.132  stbracnode2-priv

crs状态

1
crs_stat -t

检查服务状态

1
srvctl status racnode -n xxx

准备主库环境

检查数据库是否是归档模式，如果不是，则设置成归档模式，归档日志存放在+FRA下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
sys@RAC1> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

sys@RAC1> show parameter log_archive_dest_1

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ---------------------------------------------------------------------------
log_archive_dest_1                                           string

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;

srvctl stop database -d rac -o immediate
srvctl start database -d rac -o mount

sqlplus / as sysdba
ALTER DATABASE ARCHIVELOG;

sys@RAC1> alter system switch logfile;

System altered.

sys@RAC1> select name from v$archived_log;

NAME
---------------------------------------------------------------------------
+FRA/RAC/ARCHIVELOG/2019_04_17/thread_1_seq_2.261.1005831111

开启数据库的强制日志选项

1
2
3
4
5
6
7
8
9
sys@RAC1> SELECT FORCE_LOGGING FROM V$DATABASE;

FORCE_LOGGING
---------------------------------------
NO

sys@RAC1> ALTER DATABASE FORCE LOGGING;

Database altered.

确定所需的standby redo logfile数量，可以通过以下公式得出

(maximum number of redo logfiles +1) * number of threads

根据下面语句得出的结果，可以看到需要(4+1)&#42;2=10组standby redo logfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG ORDER BY 1,2;
col INSTANCE format a5

    GROUP#         MB
---------- ----------
         1        200
         2        200
         3        200
         4        200

sys@RAC1> sys@RAC1> SELECT DISTINCT THREAD#, INSTANCE FROM GV$THREAD ORDER BY 1,2;

   THREAD# INSTA
---------- -----
         1 rac1
         2 rac2

创建standby redo logfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+FRA' SIZE 200M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+FRA' SIZE 200M ;

--检查
SELECT INST_ID, GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS
   FROM GV$STANDBY_LOG ORDER BY 2,1;

   INST_ID     GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- ---------- --- ----------
         1          5          1          0 YES UNASSIGNED
         2          5          1          0 YES UNASSIGNED
         1          6          1          0 YES UNASSIGNED
         2          6          1          0 YES UNASSIGNED
         1          7          1          0 YES UNASSIGNED
         2          7          1          0 YES UNASSIGNED
         1          8          1          0 YES UNASSIGNED
         2          8          1          0 YES UNASSIGNED
         1          9          1          0 YES UNASSIGNED
         2          9          1          0 YES UNASSIGNED
         1         10          2          0 YES UNASSIGNED
         2         10          2          0 YES UNASSIGNED
         1         11          2          0 YES UNASSIGNED
         2         11          2          0 YES UNASSIGNED
         1         12          2          0 YES UNASSIGNED
         2         12          2          0 YES UNASSIGNED
         1         13          2          0 YES UNASSIGNED
         2         13          2          0 YES UNASSIGNED
         1         14          2          0 YES UNASSIGNED
         2         14          2          0 YES UNASSIGNED

在主集群上两台机器上均配置tnsnames.ora,这里备库只填写一号节点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

STBRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stbracnode1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

在备集群一号节点上配置tnsnames.ora,添加下面的连接描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

STBRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stbracnode1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)(UR=A)                >>>>==== UR=A能让监听处于blocked等状态时客户端连接上来
    )
  )

在备集群二号节点上配置tnsnames.ora

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

STBRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stbracnode2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac)
    )
  )

在所有机器上测试连接

1
2
tnsping rac
tnsping stbrac

在主库上配置dataguard相关参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
show parameter DB_UNIQUE_NAME

# 注意参数大小写敏感:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=stbrac ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbrac' SCOPE=BOTH SID='*';

alter system set log_archive_dest_state_1 = ENABLE SCOPE=BOTH SID='*';
alter system set log_archive_dest_state_2 = ENABLE SCOPE=BOTH SID='*';

# 应该返回EXCLUSIVE
show parameter REMOTE_LOGIN_PASSWORDFILE

# FAL = fetch archive log
ALTER SYSTEM SET FAL_SERVER='stbrac' SCOPE=BOTH SID='*';
ALTER SYSTEM SET FAL_CLIENT='rac' SCOPE=BOTH SID='*';

# 如果主备库文件目录一致，则不用设置
show parameter DB_FILE_NAME_CONVERT
show parameter STANDBY_FILE_MANAGEMENT
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH SID='*';

# 设置ARCn进程最大值 ( default 4):
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8 SCOPE=BOTH SID='*';

主库开启数据库闪回

1
2
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880 scope=BOTH;
ALTER DATABASE FLASHBACK ON;

在所有节点配置sqlnet.ora,添加下面配置

1
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

准备备库环境

在备集群的一号节点上，创建相关目录。因为都采用的ASM存储，所以要进到ASM目录下去创建

1
2
3
4
5
6
7
su - grid
asmcmd
cd DATA
mkdir stbrac

-- 在两个节点上都创建目录
mkdir -p /u01/app/oracle/admin/stbrac/{adump,cdump}

在stbracnode1上，创建initstbrac1.ora参数文件，先只用填写DB_NAME即可

1
echo 'DB_NAME=rac' > $ORACLE_HOME/dbs/initstbrac1.ora

在racnode1上，将密码文件复制到备集群的两个节点。密码文件目前存放在ASM当中，通过asmcmd工具拷贝到文件系统当中，然后传输到备集群

1
2
3
4
5
6
7
8
9
10
11
12
13
14
su - grid
asmcmd pwget --dbuniquename rac

asmcmd pwcopy +DATA/RAC/PASSWORD/pwdrac.256.1005822021 /tmp/orapwstbrac

# racnode1
scp /tmp/orapwstbrac stbracnode1:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstbrac1
scp /tmp/orapwstbrac stbracnode2:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstbrac2

# stbracnode1
chown oracle:oinstall /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstbrac1

# stbracnode2
chown oracle:oinstall /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstbrac2

在stbracnode1上创建静态监听

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
su - grid
cp listener.ora listener.ora.bak
vim listener.ora

--添加下面这段
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=rac)
          (SID_NAME=stbrac1)
          (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
        )
   )

-- 重启监听
srvctl stop listener
srvctl start listener
lsnrctl services | grep stbrac

创建备库

启动stbracnode1上的实例到nomount状态

1
2
3
4
5
6
7
su - oracle
sqlplus / as sysdba

export ORACLE_SID=stbrac1
startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initstbrac1.ora';

create spfile from pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initstbrac1.ora';

通过rman duplicate来创建备库，这个命令可以从主库执行也可以从备库执行。

如果duplicate命令在关掉辅助实例以后失败了，你必须重新关闭和启动备库，而且重新执行之前要先删掉你所建好的spfile 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
rman target sys/oracle@rac auxiliary sys/oracle@stbrac

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel stbc1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
set 'db_unique_name'='stbrac'
set instance_name='stbrac1'
set instance_number='1'
set db_create_online_log_dest_1='+FRA'
set db_create_online_log_dest_2='+DATA'
set db_recovery_file_dest='+FRA'
set audit_file_dest='/u01/app/oracle/admin/stbrac/adump'
set core_dump_dest='/u01/app/oracle/admin/stbrac/cdump'
nofilenamecheck
dorecover;
}

详细日志：点击这里

检查备库实例是否运行正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
sys@STBRAC1> SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE FROM GV$DATABASE;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
RAC       stbrac                         PHYSICAL STANDBY MOUNTED

COL DATAFILE FORMAT a100
SELECT INST_ID, NAME DATAFILE FROM GV$DATAFILE;

   INST_ID DATAFILE
---------- ----------------------------------------------------------------------------------------------------
         1 +DATA/STBRAC/DATAFILE/system.263.1005926817
         1 +DATA/STBRAC/DATAFILE/sysaux.260.1005926819
         1 +DATA/STBRAC/DATAFILE/undotbs1.301.1005926851
         1 +DATA/STBRAC/86B2264711E008FCE0537992A8C0E982/DATAFILE/system.290.1005926841
         1 +DATA/STBRAC/86B2264711E008FCE0537992A8C0E982/DATAFILE/sysaux.291.1005926833
         1 +DATA/STBRAC/DATAFILE/users.284.1005926855
         1 +DATA/STBRAC/86B2264711E008FCE0537992A8C0E982/DATAFILE/undotbs1.262.1005926847
         1 +DATA/STBRAC/DATAFILE/undotbs2.300.1005926855
         1 +DATA/STBRAC/86B2433955FB2449E0537992A8C02B22/DATAFILE/system.264.1005926841
         1 +DATA/STBRAC/86B2433955FB2449E0537992A8C02B22/DATAFILE/sysaux.293.1005926833
         1 +DATA/STBRAC/86B2433955FB2449E0537992A8C02B22/DATAFILE/undotbs1.261.1005926849
         1 +DATA/STBRAC/86B2433955FB2449E0537992A8C02B22/DATAFILE/undo_2.303.1005926849
         1 +DATA/STBRAC/86B2433955FB2449E0537992A8C02B22/DATAFILE/users.283.1005926855
         1 +DATA/STBRAC/DATAFILE/test.302.1005926851


sys@STBRAC1> SELECT INST_ID,GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM GV$STANDBY_LOG ORDER BY 1,2,3;

   INST_ID     GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- ---------- --- ----------
         1          5          1          0 YES UNASSIGNED
         1          6          1          0 YES UNASSIGNED
         1          7          1          0 YES UNASSIGNED
         1          8          1          0 YES UNASSIGNED
         1          9          1          0 YES UNASSIGNED
         1         10          2          0 YES UNASSIGNED
         1         11          2          0 YES UNASSIGNED
         1         12          2          0 YES UNASSIGNED
         1         13          2          0 YES UNASSIGNED
         1         14          2          0 YES UNASSIGNED


sys@STBRAC1> SELECT TYPE, count(*) FROM GV$LOGFILE GROUP BY TYPE;

TYPE      COUNT(*)
------- ----------
ONLINE           8
STANDBY         20

可以看到standby redo logfile 有很多重复，删除重复项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
column member format a50
select group#, member from v$logfile where type='STANDBY' order by 1,2;

    GROUP# MEMBER
---------- --------------------------------------------------
         5 +DATA/STBRAC/ONLINELOG/group_5.278.1005926909
         5 +FRA/STBRAC/ONLINELOG/group_5.280.1005926905
         6 +DATA/STBRAC/ONLINELOG/group_6.277.1005926915
         6 +FRA/STBRAC/ONLINELOG/group_6.279.1005926911
         7 +DATA/STBRAC/ONLINELOG/group_7.276.1005926921
         7 +FRA/STBRAC/ONLINELOG/group_7.278.1005926917
         8 +DATA/STBRAC/ONLINELOG/group_8.275.1005926925
         8 +FRA/STBRAC/ONLINELOG/group_8.277.1005926923
         9 +DATA/STBRAC/ONLINELOG/group_9.274.1005926931
         9 +FRA/STBRAC/ONLINELOG/group_9.276.1005926929
        10 +DATA/STBRAC/ONLINELOG/group_10.273.1005926937
        10 +FRA/STBRAC/ONLINELOG/group_10.272.1005926935
        11 +DATA/STBRAC/ONLINELOG/group_11.272.1005926943
        11 +FRA/STBRAC/ONLINELOG/group_11.273.1005926939
        12 +DATA/STBRAC/ONLINELOG/group_12.271.1005926947
        12 +FRA/STBRAC/ONLINELOG/group_12.295.1005926945
        13 +DATA/STBRAC/ONLINELOG/group_13.314.1005926953
        13 +FRA/STBRAC/ONLINELOG/group_13.296.1005926951
        14 +DATA/STBRAC/ONLINELOG/group_14.315.1005926959
        14 +FRA/STBRAC/ONLINELOG/group_14.297.1005926957

set linesize 150
set pagesize 100
select 'alter database drop standby logfile member ''' || member || ''';'
from v$logfile where type='STANDBY' and member like '+DATA%';

'ALTERDATABASEDROPSTANDBYLOGFILEMEMBER'''||MEMBER||''';'
------------------------------------------------------------------------------------------------------------------------------------------------------
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_5.278.1005926909';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_6.277.1005926915';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_7.276.1005926921';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_8.275.1005926925';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_9.274.1005926931';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_10.273.1005926937';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_11.272.1005926943';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_12.271.1005926947';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_13.314.1005926953';
alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_14.315.1005926959';

-- 确认删除完毕
sys@STBRAC1> SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;

    GROUP# MEMBER
---------- --------------------------------------------------
         5 +FRA/STBRAC/ONLINELOG/group_5.280.1005926905
         6 +FRA/STBRAC/ONLINELOG/group_6.279.1005926911
         7 +FRA/STBRAC/ONLINELOG/group_7.278.1005926917
         8 +FRA/STBRAC/ONLINELOG/group_8.277.1005926923
         9 +FRA/STBRAC/ONLINELOG/group_9.276.1005926929
        10 +FRA/STBRAC/ONLINELOG/group_10.272.1005926935
        11 +FRA/STBRAC/ONLINELOG/group_11.273.1005926939
        12 +FRA/STBRAC/ONLINELOG/group_12.295.1005926945
        13 +FRA/STBRAC/ONLINELOG/group_13.296.1005926951
        14 +FRA/STBRAC/ONLINELOG/group_14.297.1005926957

设置备库的参数

1
2
3
4
5
ALTER SYSTEM SET FAL_SERVER=rac SCOPE=BOTH SID='*';
ALTER SYSTEM SET FAL_CLIENT=stbrac SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SID='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=rac ASYNC DB_UNIQUE_NAME=rac VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' SCOPE=BOTH SID='*';

注册备库到ocr

1
2
3
4
su - oracle 
srvctl add database -d stbrac -o $ORACLE_HOME -role physical_standby -startoption mount -diskgroup DATA,FRA
srvctl add instance -d stbrac -i stbrac1 -n stbracnode1
srvctl add instance -d stbrac -i stbrac2 -n stbracnode2

将备库的spfile从文件系统中导入asm

通过pfile生成spfile

1
2
create pfile from spfile;
create spfile='+data/stbrac/PARAMETERFILE/spfilestbrac.ora' from pfile;

修改pfile指向spfile

1
2
3
4
5
echo "SPFILE='+data/stbrac/PARAMETERFILE/spfilestbrac.ora'" > $ORACLE_HOME/dbs/initstbrac1.ora
rm $ORACLE_HOME/dbs/spfilestbrac1.ora

-- stbracnode2
echo "SPFILE='+data/stbrac/PARAMETERFILE/spfilestbrac.ora'" > $ORACLE_HOME/dbs/initstbrac2.ora

更新OCR中的spfile信息

1
srvctl modify database -d stbrac -p +DATA/STBRAC/PARAMETERFILE/spfilestbrac.ora

重启备库实例

1
2
3
4
5
6
7
8
9
srvctl stop instance -d stbrac -i stbrac1
srvctl start instance -d stbrac -i stbrac1
srvctl status database -d stbrac

SQL> show parameter spfile;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile                   string  +DATA/STBRAC/PARAMETERFILE/spfilestbrac.ora

修改spfile中的INSTANCE_NAME和INSTANCE_NUMBER参数

1
2
3
4
ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='stbrac1';
ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='stbrac2';
ALTER SYSTEM SET INSTANCE_NAME='stbrac1' SCOPE=SPFILE SID='stbrac1';
ALTER SYSTEM SET INSTANCE_NAME='stbrac2' SCOPE=SPFILE SID='stbrac2';

启动备库的第二个实例

1
2
srvctl start instance -d stbrac -i stbrac2
srvctl status database -d stbrac

开启备库的日志应用进程

1
alter database recover managed standby database using current logfile disconnect from session;

检查每个节点的dg配置情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select inst_id, database_role, db_unique_name instance, open_mode, protection_mode, protection_level from gv$database;

   INST_ID DATABASE_ROLE    INSTANCE               OPEN_MODE        PROTECTION_MODE      PROTECTION_LEVEL
---------- ---------------- ------------------------------ -------------------- -------------------- --------------------
     1 PHYSICAL STANDBY stbrac             READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
     2 PHYSICAL STANDBY stbrac             READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


select inst_id, database_mode, recovery_mode, protection_mode
from gv$archive_dest_status
where dest_name ='LOG_ARCHIVE_DEST_1';

   INST_ID DATABASE_MODE   RECOVERY_MODE       PROTECTION_MODE
---------- --------------- ----------------------- --------------------
     2 OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
     1 OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE

检查redo传输服务

1
2
3
4
5
6
7
8
9
10
11
alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
select inst_id, sequence#, applied, first_time, next_time
from gv$archived_log order by 2,1,4;

-- 主库建表
create table test as select * from dba_tables where 1=2;

-- 检查备库
SQL> select * from test;

no rows selected

备库检查dg统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
set linesize 300
col start_time format a20
col item format a20
select to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, item , sofar, units
from v$recovery_progress
where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');

START_TIME       ITEM              SOFAR UNITS
-------------------- -------------------- ---------- --------------------------------
2019-04-18 17:26:33  Active Apply Rate      1148 KB/sec
2019-04-18 17:26:33  Average Apply Rate       58 KB/sec
2019-04-18 17:26:33  Redo Applied         22 Megabytes



col name for a13
col value for a13
col unit for a30
set lines 132
select name, value, unit, time_computed
from v$dataguard_stats where name in ('transport lag', 'apply lag');

NAME          VALUE     UNIT               TIME_COMPUTED
------------- ------------- ------------------------------ ------------------------------
transport lag +00 00:00:00  day(2) to second(0) interval   04/18/2019 17:33:57
apply lag     +00 00:00:00  day(2) to second(0) interval   04/18/2019 17:33:57

优化配置

配置主库归档日志删除策略

1
2
rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

启用备库的闪回

1
2
3
4
alter system set db_flashback_retention_target =2880;
alter database recover managed standby database cancel;
alter database flashback on;
alter database recover managed standby database using current logfile disconnect from session;

配置dg broker

查看系统现有dg broker配置

1
2
3
4
5
6
7
sys@RAC1> show parameter dg_broker

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- -------------------------------------------------------------------------------------
dg_broker_config_file1                                       string      /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1rac.dat
dg_broker_config_file2                                       string      /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2rac.dat
dg_broker_start                                              boolean     FALSE

rac环境中dgbroker的配置文件要存放在共享目录上，在ASM上新建一个目录

1
2
3
su - grid
ASMCMD>cd +data/rac/
ASMCMD>mkdir DGCONFIG

停掉备库apply

1
alter database recover managed standby database cancel;

修改主库参数

1
2
3
4
alter system set LOG_ARCHIVE_DEST_2='' scope=both;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/RAC/DGCONFIG/dr1rac.dat' SCOPE=spfile sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/RAC/DGCONFIG/dr2rac.dat' SCOPE=spfile sid='*';
alter system set dg_broker_start=true scope=both sid='*';

同时也修改备库

1
2
3
4
5
6
7
8
su - grid
ASMCMD>cd +data/stbrac/
ASMCMD>mkdir DGCONFIG

alter system set LOG_ARCHIVE_DEST_2='' scope=both;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/STBRAC/DGCONFIG/dr1stbrac.dat' SCOPE=spfile sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/STBRAC/DGCONFIG/dr2stbrac.dat' SCOPE=spfile sid='*';
alter system set dg_broker_start=true scope=both sid='*';

重启主备库

1
2
3
4
5
srvctl stop database -d rac
srvctl start database -d rac

srvctl stop database -d stbrac
srvctl start database -d stbrac

创建dgbroker配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
su - oracle
dgmgrl>
create configuration 'raccfg' as primary database is 'rac' connect identifier is 'rac';
add database 'stbrac' as connect identifier is 'stbrac';
ENABLE configuration;
enable database 'rac';
enable database 'stbrac';


DGMGRL> show configuration

Configuration - raccfg

  Protection Mode: MaxPerformance
  Members:
  rac    - Primary database
    stbrac - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 17 seconds ago)

测试通过dgbroker做swithover切换操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DGMGRL> switchover to 'stbrac';
Performing switchover NOW, please wait...
Operation requires a connection to database "stbrac"
Connecting ...
Connected to "stbrac"
Connected as SYSDBA.
New primary database "stbrac" is opening...
Oracle Clusterware is restarting database "rac" ...
Connected to "rac"
Connected to "rac"
Switchover succeeded, new primary is "stbrac"
DGMGRL> show configuration;

Configuration - raccfg

  Protection Mode: MaxPerformance
  Members:
  stbrac - Primary database
    rac    - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 110 seconds ago)

--重新swithover回来
DGMGRL> switchover to 'rac';
Performing switchover NOW, please wait...
Operation requires a connection to database "rac"
Connecting ...
Connected to "rac"
Connected as SYSDBA.
New primary database "rac" is opening...
Oracle Clusterware is restarting database "stbrac" ...
Switchover succeeded, new primary is "rac"

至此整个安装配置过程结束。

发表 xbdba Troubleshooting3 分钟读完 (大约393个字)

ORA-15001: diskgroup "FRA" does not exist or is not mounted

一台数据库在启动时候报错,提示一个asm group不存在

1
2
3
4
5
6
7
8
9
10
11
12
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+FRA/STBRAC/CONTROLFILE/current.294.1005908473'
ORA-17503: ksfdopn:2 Failed to open file +FRA/STBRAC/CONTROLFILE/current.294.1005908473
ORA-15001: diskgroup "FRA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete
ORA-205 signalled during: alter database mount...


WARNING: failed to open a disk[/dev/asmdiskd]
ORA-15025: could not open disk "/dev/asmdiskd"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied

检查asm的磁盘等信息均正常

1
2
3
4
5
6
7
8
9
10
11
[grid@stbracnode1 ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     51200    16784                0           16784              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     61440    55336                0           55336              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     20480    17404                0           17404              0             N  FRA/

[grid@stbracnode1 ~]$ ll /dev/asm*
brw-rw---- 1 grid asmadmin 8, 16 Apr 18 13:22 /dev/asmdiskb
brw-rw---- 1 grid asmadmin 8, 32 Apr 18 13:22 /dev/asmdiskc
brw-rw---- 1 grid asmadmin 8, 48 Apr 18 13:22 /dev/asmdiskd

查询MOS说是权限出现了问题，ORACLE_HOME目录下的oracle二进制文件的属主应该是asmadmin，而不是oinstall。

1
2
[oracle@ ~]$ ll /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 407944928 Apr 16 15:53 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle

查看其它正常的asm节点

1
2
[oracle@ dbs]$ ll /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 407944928 Apr 16 15:48 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle

这台集群刚刚进行了grid和database的补丁升级，应该是由于这个原因导致属主进行了变更

知道问题原因以后，解决办法就简单了,切换到grid用户下

1
2
3
4
setasmgidwrap o=/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle

[grid@ ~]$ ll /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 407944928 Apr 16 15:56 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle

重新启动数据库，问题解决

1
2
3
4
5
6
7
8
9
SQL> startup mount
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size          8795904 bytes
Variable Size         754977024 bytes
Database Buffers     1744830464 bytes
Redo Buffers            7979008 bytes
Database mounted.
发表 xbdba 安装/升级15 分钟读完 (大约2193个字)

ORA-00845: MEMORY_TARGET not supported on this system

12.2安装grid完毕后执行root.sh时报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
[root@racnode1 ~]# /u01/app/12.2.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/12.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/12.2.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/racnode1/crsconfig/rootcrs_racnode1_2019-04-11_10-21-10AM.log
2019/04/11 10:21:12 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2019/04/11 10:21:12 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
2019/04/11 10:21:46 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2019/04/11 10:21:46 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'.
2019/04/11 10:21:52 CLSRSC-363: User ignored prerequisites during installation
2019/04/11 10:21:52 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'.
2019/04/11 10:21:54 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'.
2019/04/11 10:21:55 CLSRSC-594: Executing installation step 5 of 19: 'SaveParamFile'.
2019/04/11 10:22:08 CLSRSC-594: Executing installation step 6 of 19: 'SetupOSD'.
2019/04/11 10:22:09 CLSRSC-594: Executing installation step 7 of 19: 'CheckCRSConfig'.
2019/04/11 10:22:09 CLSRSC-594: Executing installation step 8 of 19: 'SetupLocalGPNP'.
2019/04/11 10:23:00 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'.
2019/04/11 10:23:12 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'.
2019/04/11 10:23:12 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'.
2019/04/11 10:23:18 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'.
2019/04/11 10:23:34 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2019/04/11 10:23:55 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'.
2019/04/11 10:24:02 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2019/04/11 10:24:24 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'.
2019/04/11 10:24:36 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.evmd' on 'racnode1'
CRS-2672: Attempting to start 'ora.mdnsd' on 'racnode1'
CRS-2676: Start of 'ora.mdnsd' on 'racnode1' succeeded
CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'racnode1'
CRS-2676: Start of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1'
CRS-2672: Attempting to start 'ora.gipcd' on 'racnode1'
CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'racnode1'
CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1'
CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded
CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded

ASM failed to start. Check /u01/app/grid/cfgtoollogs/asmca/asmca-190411AM102519.log for details.

2019/04/11 10:25:41 CLSRSC-184: Configuration of ASM failed
2019/04/11 10:25:47 CLSRSC-258: Failed to configure and start ASM
Died at /u01/app/12.2.0/grid/crs/install/crsinstall.pm line 2091.
The command '/u01/app/12.2.0/grid/perl/bin/perl -I/u01/app/12.2.0/grid/perl/lib -I/u01/app/12.2.0/grid/crs/install /u01/app/12.2.0/grid/crs/install/rootcrs.pl ' execution failed

查看日志有报错

1
2
3
4
5
[main] [ 2019-04-11 10:25:29.659 CST ] [OracleHome.getVersion:1152]  Current version from sqlplus: 12.2.0.1.0
[main] [ 2019-04-11 10:25:29.659 CST ] [UsmcaLogger.logInfo:156]  Role SYSASM
[main] [ 2019-04-11 10:25:29.659 CST ] [UsmcaLogger.logInfo:156]  OS Auth true
[main] [ 2019-04-11 10:25:40.692 CST ] [SQLEngine.done:2314]  Done called
[main] [ 2019-04-11 10:25:40.695 CST ] [USMInstance.configureLocalASM:3367]  ORA-00845: MEMORY_TARGET not supported on this system

这个错误以前启动数据库的时候碰见过，就是/dev/shm太小了，这个值至少要比MAX_MEMORY_TARGET大。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[root@racnode1 trace]# df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             980M     0  980M   0% /dev
tmpfs                993M     0  993M   0% /dev/shm
tmpfs                993M  8.9M  984M   1% /run
tmpfs                993M     0  993M   0% /sys/fs/cgroup
/dev/mapper/ol-root   58G   11G   48G  18% /
/dev/sda1            253M  139M  115M  55% /boot
tmpfs                199M     0  199M   0% /run/user/0

[root@racnode1 trace]# mount -o remount,size=4g /dev/shm
[root@racnode1 trace]# df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             980M     0  980M   0% /dev
tmpfs                4.0G     0  4.0G   0% /dev/shm
tmpfs                993M  8.9M  984M   1% /run
tmpfs                993M     0  993M   0% /sys/fs/cgroup
/dev/mapper/ol-root   58G   11G   48G  18% /
/dev/sda1            253M  139M  115M  55% /boot
tmpfs                199M     0  199M   0% /run/user/0

-- 添加到/etc/fstab

tmpfs /dev/shm tmpfs defaults,size=4G 0 0

卸载已安装的crs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@racnode1 trace]# /u01/app/12.2.0/grid/crs/install/rootcrs.sh  -deconfig -force
Using configuration parameter file: /u01/app/12.2.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/racnode1/crsconfig/crsdeconfig_racnode1_2019-04-11_11-06-32AM.log
PRCR-1070 : Failed to check if resource ora.net1.network is registered
CRS-0184 : Cannot communicate with the CRS daemon.
PRCR-1070 : Failed to check if resource ora.helper is registered
CRS-0184 : Cannot communicate with the CRS daemon.
PRCR-1070 : Failed to check if resource ora.ons is registered
CRS-0184 : Cannot communicate with the CRS daemon.

2019/04/11 11:06:45 CLSRSC-180: An error occurred while executing the command '/u01/app/12.2.0/grid/bin/srvctl config nodeapps'
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'racnode1'
CRS-2679: Attempting to clean 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1'
CRS-2677: Stop of 'ora.mdnsd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'racnode1'
CRS-2677: Stop of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2681: Clean of 'ora.asm' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'racnode1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
2019/04/11 11:07:16 CLSRSC-4006: Removing Oracle Trace File Analyzer (TFA) Collector.
2019/04/11 11:09:01 CLSRSC-4007: Successfully removed Oracle Trace File Analyzer (TFA) Collector.
2019/04/11 11:09:02 CLSRSC-336: Successfully deconfigured Oracle Clusterware stack on this node

重新执行root.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
[root@racnode1 trace]# /u01/app/12.2.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/12.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/12.2.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/racnode1/crsconfig/rootcrs_racnode1_2019-04-11_11-09-55AM.log
2019/04/11 11:09:58 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2019/04/11 11:09:58 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
2019/04/11 11:10:29 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2019/04/11 11:10:29 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'.
2019/04/11 11:10:34 CLSRSC-363: User ignored prerequisites during installation
2019/04/11 11:10:34 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'.
2019/04/11 11:10:37 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'.
2019/04/11 11:10:38 CLSRSC-594: Executing installation step 5 of 19: 'SaveParamFile'.
2019/04/11 11:10:46 CLSRSC-594: Executing installation step 6 of 19: 'SetupOSD'.
2019/04/11 11:10:46 CLSRSC-594: Executing installation step 7 of 19: 'CheckCRSConfig'.
2019/04/11 11:10:46 CLSRSC-594: Executing installation step 8 of 19: 'SetupLocalGPNP'.
2019/04/11 11:11:24 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'.
2019/04/11 11:11:33 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'.
2019/04/11 11:11:33 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'.
2019/04/11 11:11:39 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'.
2019/04/11 11:11:55 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2019/04/11 11:12:15 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'.
2019/04/11 11:12:20 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2019/04/11 11:12:42 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'.
2019/04/11 11:12:48 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.evmd' on 'racnode1'
CRS-2672: Attempting to start 'ora.mdnsd' on 'racnode1'
CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'racnode1'
CRS-2676: Start of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1'
CRS-2672: Attempting to start 'ora.gipcd' on 'racnode1'
CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'racnode1'
CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1'
CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded
CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded

Disk groups created successfully. Check /u01/app/grid/cfgtoollogs/asmca/asmca-190411AM111328.log for details.


2019/04/11 11:20:23 CLSRSC-482: Running command: '/u01/app/12.2.0/grid/bin/ocrconfig -upgrade grid oinstall'
CRS-2672: Attempting to start 'ora.crf' on 'racnode1'
CRS-2672: Attempting to start 'ora.storage' on 'racnode1'
CRS-2676: Start of 'ora.storage' on 'racnode1' succeeded
CRS-2676: Start of 'ora.crf' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'racnode1'
CRS-2676: Start of 'ora.crsd' on 'racnode1' succeeded
CRS-4256: Updating the profile
Successful addition of voting disk d5d63504c2084fd1bf20c9109c45188c.
Successfully replaced voting disk group with +DATA.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   d5d63504c2084fd1bf20c9109c45188c (/dev/asmdiskf) [DATA]
Located 1 voting disk(s).
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode1'
CRS-2677: Stop of 'ora.crsd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.storage' on 'racnode1'
CRS-2673: Attempting to stop 'ora.crf' on 'racnode1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'racnode1'
CRS-2677: Stop of 'ora.crf' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.storage' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.mdnsd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'racnode1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1'
CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1'
CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'racnode1'
CRS-2677: Stop of 'ora.gipcd' on 'racnode1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
2019/04/11 11:26:42 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'racnode1'
CRS-2672: Attempting to start 'ora.evmd' on 'racnode1'
CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'racnode1'
CRS-2676: Start of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'racnode1'
CRS-2676: Start of 'ora.gipcd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1'
CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'racnode1'
CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1'
CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded
CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'racnode1'
CRS-2672: Attempting to start 'ora.ctssd' on 'racnode1'
CRS-2676: Start of 'ora.ctssd' on 'racnode1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'racnode1'
CRS-2676: Start of 'ora.asm' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'racnode1'
CRS-2676: Start of 'ora.storage' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.crf' on 'racnode1'
CRS-2676: Start of 'ora.crf' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'racnode1'
CRS-2676: Start of 'ora.crsd' on 'racnode1' succeeded
CRS-6023: Starting Oracle Cluster Ready Services-managed resources
CRS-6017: Processing resource auto-start for servers: racnode1
CRS-6016: Resource auto-start has completed for server racnode1
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2019/04/11 11:31:30 CLSRSC-343: Successfully started Oracle Clusterware stack
2019/04/11 11:31:33 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'.
CRS-2672: Attempting to start 'ora.ASMNET1LSNR_ASM.lsnr' on 'racnode1'
CRS-2676: Start of 'ora.ASMNET1LSNR_ASM.lsnr' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'racnode1'
CRS-2676: Start of 'ora.asm' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.DATA.dg' on 'racnode1'
CRS-2676: Start of 'ora.DATA.dg' on 'racnode1' succeeded
2019/04/11 11:43:18 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'.
2019/04/11 11:49:21 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded
发表 xbdba 备份恢复25 分钟读完 (大约3816个字)

12c中rman恢复表和分区

在12c以前的版本中，如果想恢复单表到某个时间点之前是一件非常麻烦的事情，一般方法可能只能通过把全库备份基于某个时间点恢复到别处，然后再从新库当中把数据导出来插入回原库中，如果源库很大的话整个时间可想而知。而在12c新的rman特性中，提供了一个非常棒的功能，可以直接通过简单的命令就能恢复单表或者分区到某个时间点，还可以直接恢复到其他的schema下面。

如果想通过rman备份当中恢复出表或者分区时，主要是做了以下步骤：

  • 根据recover指定的目标时间，来判断哪些备份包含需要恢复的表或者分区
  • 判断目标主机上是否含有足够的空间来创建辅助实例，用于恢复过程使用。如果空间不足，rman进程会报错然后退出recover
  • 在目标主机上创建辅助实例，基于指定的恢复时间点，恢复指定的表或分区到辅助实例当中
  • 创建包含需要恢复的表或分区的数据泵导出文件，可以指定文件的名称和路径，主要用于存放表和分区的元数据信息
  • (可选)将数据泵文件导入目标库，这个操作你可以之后手动操作
  • (可选)可以将恢复的表或分区导入新库当中的不同用户和不同表空间下

查看当前的数据文件和备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
[oracle@xb ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 26 13:14:19 2019

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

connected to target database: ORA12C (DBID=380590835)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORA12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u01/app/oracle/oradata/ora12c/system01.dbf
3    500      SYSAUX               NO      /u01/app/oracle/oradata/ora12c/sysaux01.dbf
4    70       UNDOTBS1             YES     /u01/app/oracle/oradata/ora12c/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
6    330      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/ora12c/users01.dbf
8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf
9    250      PDB12C:SYSTEM        YES     /u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf
10   360      PDB12C:SYSAUX        NO      /u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf
11   100      PDB12C:UNDOTBS1      YES     /u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf
12   5        PDB12C:USERS         NO      /u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf
13   70       PDB12C:TBS1          NO      /u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf
15   10       PDB12C:TBS2          NO      /u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    131      TEMP                 32767       /u01/app/oracle/oradata/ora12c/temp01.dbf
2    64       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ora12c/pdbseed/temp012019-03-13_09-14-08-192-AM.dbf
3    130      PDB12C:TEMP          32767       /u01/app/oracle/oradata/ora12c/pdb12c/temp01.dbf

RMAN> list backup;

specification does not match any backup in the repository




-- 当前并没有可用备份，对CDB和它的PDBs做一个全备

RMAN> backup database plus archivelog;


Starting backup at 26-MAR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1002984384
input archived log thread=1 sequence=4 RECID=2 STAMP=1002984802
input archived log thread=1 sequence=5 RECID=3 STAMP=1003929462
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/02ttdfrm_1_1 tag=TAG20190326T131742 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-MAR-19

Starting backup at 26-MAR-19
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/ora12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora12c/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora12c/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ora12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/05ttdfsc_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-MAR-19

Starting backup at 26-MAR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=4 STAMP=1003929491
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/06ttdfsj_1_1 tag=TAG20190326T131811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAR-19

Starting Control File and SPFILE Autobackup at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-19

构建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop table t1 purge;

create table t1 (id number);
insert into t1 values(1);
commit;

-- 检查当前scn
xb@PDB12C> select current_scn from v$database;

CURRENT_SCN
-----------
    1612170

insert into t1 values(2);
commit;

执行恢复，指定一个辅助目录，时间的指定可以通过UNTIL SCN,UNTIL TIME和UNTIL SEQUENCE，可以只导出dmp，并不立即做导入表的操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
recover table xb.t1 of pluggable database PDB12C
  until scn 1612170
  auxiliary destination '/u01/aux'                      >>>>==== 指定辅助目录，用于建立辅助实例
  datapump destination '/u01/aux'                       >>>>==== 指定datapump目录
  dump file 't1_meta.dmp'                               >>>>==== 指定datapump文件名
  remap table 'XB'.'T1':'XBDBA'.'T2'                    >>>>==== 12.2中可以恢复到不同schema
  remap tablespace 'TBS1':'TBS2';                       >>>>==== 恢复到不同tablespace


Starting recover at 26-MAR-19
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 PDB12C:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB12C:UNDOTBS1

Creating automatic instance, with SID='AEuy'

initialization parameters used for automatic instance:
db_name=ORA12C
db_unique_name=AEuy_pitr_PDB12C_ORA12C
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=504M
processes=200
db_create_file_dest=/u01/aux
log_archive_dest_1='location=/u01/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance ORA12C

Oracle instance started

Total System Global Area     528482304 bytes

Fixed Size                     8794744 bytes
Variable Size                167775624 bytes
Database Buffers             348127232 bytes
Redo Buffers                   3784704 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log 
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 26-MAR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 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/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 tag=TAG20190326T131812
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl
Finished restore at 26-MAR-19

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  9 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  11 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  10 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 9, 4, 11, 3, 10;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/aux/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 26-MAR-19
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 /u01/aux/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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 00009 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 26-MAR-19

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_system_g9mfx5j9_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_g9mfxnvd_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_undotbs1_g9mfx5jf_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_g9mfxnvf_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_sysaux_g9mfx5jc_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_g9mfxnvc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'PDB12C' "alter database datafile 
 9 online";
sql clone "alter database datafile  4 online";
sql clone 'PDB12C' "alter database datafile 
 11 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB12C' "alter database datafile 
 10 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  10 online

Starting recover at 26-MAR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf
archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6
archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-19

sql statement: alter database open read only

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB12C open read only';
}
executing Memory Script

sql statement: alter pluggable database  PDB12C open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files = 
  ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     528482304 bytes

Fixed Size                     8794744 bytes
Variable Size                167775624 bytes
Database Buffers             348127232 bytes
Redo Buffers                   3784704 bytes

sql statement: alter system set  control_files =   ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     528482304 bytes

Fixed Size                     8794744 bytes
Variable Size                167775624 bytes
Database Buffers             348127232 bytes
Redo Buffers                   3784704 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  13 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  13;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 26-MAR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK

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 00013 to /u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-MAR-19

datafile 13 switched to datafile copy
input datafile copy RECID=17 STAMP=1003929974 file name=/u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g9mfzojn_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# online the datafiles restored or switched
sql clone 'PDB12C' "alter database datafile 
 13 online";
# recover and open resetlogs
recover clone database tablespace  "PDB12C":"TBS1", "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  13 online

Starting recover at 26-MAR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf
archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6
archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-19

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB12C open';
}
executing Memory Script

sql statement: alter pluggable database  PDB12C open

contents of Memory Script:
{
# create directory for datapump import
sql 'PDB12C' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/aux''";
# create directory for datapump export
sql clone 'PDB12C' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/aux''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_AEuy_Aopu":  
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> . . exported "XB"."T1"                                   5.046 KB       1 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_AEuy_Aopu" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_AEuy_Aopu is:
   EXPDP>   /u01/aux/t1_meta.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_AEuy_Aopu" successfully completed at Tue Mar 26 13:28:07 2019 elapsed 0 00:00:57
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_AEuy_ovnl" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_AEuy_ovnl":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "XBDBA"."T2"                                5.046 KB       1 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Job "SYS"."TSPITR_IMP_AEuy_ovnl" successfully completed at Tue Mar 26 13:28:21 2019 elapsed 0 00:00:05
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_g9mfy1gj_.tmp deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_temp_g9mfxz6f_.tmp deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_3_g9mfzxjf_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_2_g9mfzrc3_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_1_g9mfzr7d_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g9mfzojn_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_g9mfxnvc_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_sysaux_g9mfx5jc_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_g9mfxnvf_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_undotbs1_g9mfx5jf_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_g9mfxnvd_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_system_g9mfx5j9_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl deleted
auxiliary instance file t1_meta.dmp deleted
Finished recover at 26-MAR-19

重新检查当前数据

1
2
3
4
5
xb@PDB12C> select * from xbdba.t2;

        ID
----------
         1

12.2中，在做recover表或分区的时候，因为需要创建辅助实例，所以会提前检查可以空间，如果指定的辅助目录空间不足，则操作无法进行。

发表 xbdba 12c新特性6 分钟读完 (大约970个字)

12c Create Table for Exchange Partition

以前版本中如果我们要对一个分区表做EXCHANGE PARTITION时，都会通过create table as select * XXX来新建一张普通表用于exchange，但是可能遇到这样的情况，源表的部分字段被禁用或不可见，则会导致exchange的时候失败，原因是因为两个表的表结构不一致。

而在新的12.2版本中，提供了一种新的建表方法，通过FOR EXCHANGE WITH语句来确保做exchange分区操作时的表与分区表形状一致，当然这种建表的方法并不会同步建索引。

下面总结了CREATE TABLE FOR EXCHANGE WITH操作所带来的一些影响：

  • 这个DDL操作主要是为了方便创建用于做exchange partition操作的表

  • 这个操作其实就是对要做exchange表的克隆，包含字段的顺序以及属性

  • 新建的表无法重命名，都是从源表继承下来

  • 这个DDL操作中唯一一个可以改变的逻辑属性是表是否为partition

    比如源表为一个复合分区表，如果多个子分区交换到新表的分区下，这种情形时，你就可以指定新表是否需要为分区表。

    子分区与分区之间可以是非对称的，但是在建表的时候必须要精确匹配分区与子分区的对应关系。

  • 可以指定的物理属性则为表使用的段属性

  • 使用这个DDL操作可以复制的列包括不限于以下：不可用列、不可见列、虚拟列、函数索引表达式列以及其他内部设置和属性

以前版本CTAS

创建一个测试分区表作为源表，包含了几种特殊类型的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop table t1 purge;

create table t1 (
id number,
col1 varchar2(100),
col2 varchar2(100),
col3 generated always as (id+100) virtual
)
partition by range (id)
(
partition p1 values less than (200),
partition p2 values less than (400)
);

create index idx_t1 on t1(id);

分别修改col1和col2的字段属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
xb@PDB12C> alter table t1 set unused(col1);

Table altered.

xb@PDB12C> alter table t1 modify col2 invisible;

Table altered.

xb@PDB12C> @desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER                        >>>>====已经看不到col1和col2两个字段
    2      COL3                                     NUMBER

col column_name for a40
col data_type for a20
col data_length for 999999999999
col data_default for a20
col virtual_column for a20
SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'T1';

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
ID                                       NUMBER                          22                      NO
SYS_C00002_19031410:18:00$               VARCHAR2                       100                      NO
COL2                                     VARCHAR2                       100                      NO
COL3                                     NUMBER                          22 "ID"+100             YES

创建一张新的普通表用来做EXCHANGE PARTITION

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
drop table t2 purge;
create table t2 as select * from t1 where 1=2;
create index idx_t2 on t2(id);

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
ID                                       NUMBER                          22                      NO
COL3                                     NUMBER                          22                      NO                   >>>>====新表字段并非虚拟列


insert into t2 
select level from dual connect by rownum>=200;

commit;

alter table t1
  exchange partition p1
  with table t2
  without validation
  update global indexes;

ERROR at line 3:
ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

可以看到报错提示字段数量不一致，CTAS对于这种情况就不太适用。

新版本FOR EXCHANGE WITH

现在可以使用12.2新的语句CREATE TABLE ... FOR EXCHANGE WITH

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table t2 purge;

create table t2 
    for exchange with table t1;
create index idx_t2 on t2(id);

col column_name for a40
col data_type for a20
col data_length for 999999999999
col data_default for a20
col virtual_column for a20
SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'T2';

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
COL2                                     VARCHAR2                       100                      NO
SYS_C00002_19031410:18:00$               VARCHAR2                       100                      NO
ID                                       NUMBER                          22                      NO
COL3                                     NUMBER                          22 "ID"+100             YES

可以看到现在新建的T2表字段属性就跟T1完全一样，继续剩下的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
insert into t2(id)
select level  from dual connect by rownum>=200;

commit;

xb@PDB12C> 
alter table t1
  exchange partition p1
  with table t2
  without validation
   update global indexes;

Table altered.

xb@PDB12C> select * from t1 partition (p1) where rownum>=10;

        ID       COL3
---------- ----------
         1        101
         2        102
         3        103
         4        104
         5        105
         6        106
         7        107
         8        108
         9        109
        10        110

10 rows selected.

则可以顺利完成分区交换操作。

发表 xbdba Tuning19 分钟读完 (大约2905个字)

12.2统计信息之管理统计信息

除了收集合适的统计信息，提供一个管理这些统计信息的综合架构同样重要。oracle提供了多个方法去做这些工作，包括恢复统计信息到以前的版本，提供从一个系统迁移统计信息到另外一个的选项，甚至你可以手动设置统计信息。这些选项在某些特定情形下都非常有用，但是仍然建议都通过DBMS_STATS包来收集统计信息。

恢复统计信息

当你用DBMS_STATS包来收集统计信息时，原来的统计信息会自动备份存储在字典表里，如果新生成的统计信息有问题则能很容易的通过DBMS_STATS.RESTORE_TABLE_STATS恢复之前的统计信息。视图DBA_TAB_STATS_HISTORY包含多个时间点保存的统计信息。

下面这个例子恢复了表t的统计信息至昨天，并自动使shared pool里所有涉及到T表的游标都失效。因为恢复了昨天的统计信息，所以想立刻想使用昨天的统计信息来影响新的游标。参数NO_INVALIDATE的值决定了表T相关的游标是否要失效。

1
2
3
4
5
6
7
8
BEGIN 
    DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SYS', 
                                   tabname => 'T', 
                                   as_of_timestamp => SYSTIMESTAMP-1,
                                   force => FALSE, 
                                   no_invalidate => FALSE); 
END; 
/

挂起统计信息

默认情况下，当统计信息收集以后，它们将会立刻被写入到数据字典表然后立刻被优化器所使用。从11g开始，oracle支持收集完统计信息以后不立即写入字典表，而是先存储在挂起表中，这样就可以在正式使用之前先进行测试。这些挂起统计信息可以对单个会话进行启用，以一种可控的方式让你在应用这些统计信息之前先进行验证。为了激活挂起统计信息的收集，需要使用DBMS_STATS.SET_*_PREFS其中的过程来改变PUBLISH参数的值从TRUE（默认值）变成FALSE。

1
exec DBMS_STATS.SET_TABLE_PREFS('SYS', 'T', 'PUBLISH', 'FALSE');

跟之前一样正常收集统计信息

1
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');

这时收集的统计信息都会储存在USER_*_PENDING_STATS数据字典中，可以指定优化器去使用这些挂起统计信息，通过ALTER SESSION命令去设置初始化参数OPTIMIZER_USE_PENDING_STATS为TRUE然后运行sql。对于sql中那些没有挂起统计信息的表来说，优化器会自动选择它们当前存储在字典表的信息。当你启用了这些挂起统计信息，也可以通过DBMS_STATS.PUBLISH_PENDING_STATS去发布它们，将它们写入正式的数据字典当中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
sys@ORA12C> INSERT INTO T select * from t;                       >>>>==== 手动插入一些数据

55 rows created.

sys@ORA12C> commit;

Commit complete.

sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tables  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                            55          1 2018-12-13 16:58:16                      >>>>====旧的统计信息

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                           110          1 2018-12-18 15:13:14                      >>>>===挂起统计信息里显示正确的110行

更新挂起统计信息至正式表

1
2
3
4
5
6
7
8
9
10
11
12
13
sys@ORA12C> Exec dbms_stats.publish_pending_stats('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tables  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                           110          1 2018-12-18 15:13:14              >>>>====数据正确

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='T';

no rows selected

如果不想使用新的统计信息，可以直接删掉

1
Exec dbms_stats.delete_pending_stats('SYS', 'T');

导入导出统计信息

统计信息可以从一个库复制到另一个，比如在准生产环境通过从别的库复制过来的统计信息进行性能测试是很有用的。复制的时候是通过DBMS_STATS.EXPORT_*_STATS和DBMS_STATS.IMPORT_*_STATS过程来实现。

在导出统计信息之前，你需要先通过DBMS_STATS.CREATE_STAT_TABLE创建一张表来储存这些信息。当表创建完毕以后，你就可以通过DBMS_STATS.EXPORT_*_STATS过程来将统计信息导出，当这些统计信息打包到表里以后，你就可以通过数据泵的方式将表的数据从生产环境导入到测试环境。当表导入测试数据库以后，就可以通过DBMS_STATS.IMPORT_*_STATS将统计信息导入字典当中。

1
2
3
4
5
exec DBMS_STATS.CREATE_STAT_TABLE('SYS','T','USERS');

exec dbms_stats.EXPORT_DATABASE_STATS('T','ST_TEST','SYS');

exec dbms_stats.IMPORT_DATABASE_STATS('T','ST_TEST','SYS');

复制分区统计信息

当处理分区表时，优化器需要同时依赖整表和单独分区的统计信息用于得到一个更优的执行计划。如果sql只需要访问单独的分区，优化器则只采用单独分区的统计信息。如果需要访问多个分区，则优化器会采用全局统计信息。

经常会有这样的场景，分区表增加分区，数据只会插入这个新分区。如果用户在这个分区收集统计信息之前去查询这些数据，那很可能就会得到一个比较差的执行计划。又一个很常见的场景就是传入谓词条件的值超过了列统计信息的最大值和最小值之间的范围，这就是被熟知的超出范围错误。在这种情况下，优化器根据谓词与最大值之间的距离来分配选择性（假设传参超过了最大值），谓词值与最大值或最小值越远，那么值的选择性就越低。

超出范围情况能通过DBMS_STATS.COPY_TABLE_STATS过程来避免，这个过程能将源分区的统计信息复制到新建的统计信息为空的分区中去。同时复制了依赖对象的统计信息：字段、本地索引等。分区字段的最小值和最大值按照如下进行调整：

  • 如果分区类型是HASH则目标分区的最大值和最小值和源分区一致
  • 如果分区类型是LIST并且目标分区不是一个默认分区，则目标分区的最小值设成用来定义分区的LIST值中的最小值，最大值就是LIST值中的最大值
  • 如果分区类型是LIST并且目标分区是一个默认分区，则目标分区的最小值设成源分区的最小值，目标分区的最大值设成源分区的最大值
  • 如果分区类型是RANGE，则目标分区的最小值设成前一个分区的上限值，目标分区的最大值设成定义目标分区的RANGE最大值，而如果RANGE最大值是MAXVALUE时，目标分区的最大值则设成前一个分区的上限值

可以根据给定的scale_factor参数来缩放统计信息（比如块的数量、行数等）。统计信息中像行的平均长度和唯一值数量并未进行调整，而是认为在目标分区中是相同的。

将SALES_Q3_2011范围分区当中的统计信息复制到SALES_Q4_2011，设置缩放因子为2来缩放基础统计信息

1
EXEC DBMS_STATS.COPY_TABLE_STATS('SH','SALES','SALES_Q3_2002','SALES_Q4_2002', 2);

只有在索引分区名称与表分区名称一样时才会复制索引信息（默认值），全局统计信息默认情况下并不会更新。只有在全局统计信息不存在并且通过聚合生成了全局统计信息时，才能通过DBMS_STATS.COPY_TABLE_STATS过程影响全局统计信息。

统计信息比较

一个系统的执行计划于另一个系统不一样的一个重要原因就是优化器统计信息不一样。比如如果数据没有同步，测试环境的统计信息很可能就与生产环境的不一样。为了确定统计信息之间的差异，可以通过DBMS_STATS.DIFF_TABLE_STATS_*函数来比较两个源。A下面的表可以和B下面的表进行比较，同样可以对一个表的不同时间点的统计信息比较，或者当前统计信息与挂起统计信息比较。比如比较当前时间和昨天

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
sys@ORA12C> select report, maxdiffpct from dbms_stats.diff_table_stats_in_history(user, 'T', SYSDATE-1, SYSDATE, 2);

REPORT
---------------------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T
OWNER         : SYS
SOURCE A      : Statistics as of 18-DEC-18 11.03.39.000000 AM +08:00
SOURCE B      : Statistics as of 19-DEC-18 11.03.39.000000 AM +08:00
PCTTHRESHOLD  : 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

T                           T   A   55         1          3          55
                                B   110        1          3          110
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

ID              A   10      .009090909 YES  0       3    80    C10A  55
                B   10      .004545454 YES  0       3    80    C10A  110
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

比较不同库的两张表统计信息其实跟导入导出统计信息类似，也是将其中一个系统的表统计信息导出表然后导入第二个系统进行比较

最后通过dbms_stats.diff_table_stats_in_stattab完成

1
select report from dbms_stat.diff_table_stats_in_stattab( 'SCOTT', 'EMP', 'STAT_TAB_OLD', 'STAT_TAB_NEW');

DIFF函数同时比较依赖对象（索引、分区、列）的统计信息，如果统计信息之间的差异超过了阀值则会列出源端目标端对象的所有的统计信息。这个阀值可以作为入参传入，默认值是10%。计算的时候以源端统计信息作为基数。

锁定统计信息

有些情况下，你希望通过锁定表或者方案的统计信息来避免重新收集时产生影响。当统计信息被锁定后，任何操作都无法修改这些信息除非你重新解锁或则收集统计信息时设置FORCE参数为TRUE。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
sys@ORA12C> exec DBMS_STATS.LOCK_TABLE_STATS('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 8582
ORA-06512: at "SYS.DBMS_STATS", line 9461
ORA-06512: at "SYS.DBMS_STATS", line 35836
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1


sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T',force=>TRUE);

PL/SQL procedure successfully completed.

也可以锁定分区级别的统计信息

1
EXEC DBMS_STATS.LOCK_PARTITION_STATS('SH', 'SALES', 'SALES_Q3_2000');

需要注意锁定统计信息的层次问题，比如如果锁定了一个分区表统计信息，然后为了单独搜集一个分区的统计信息而将这个分区统计信息进行解锁，则会报错ORA-20005。因为即使分区锁被解锁了，表级锁仍然存在。所以为了统计单独分区只能通过设置FORCE为TRUE。

手动设置统计信息

在罕见的情况下，手动设置数据字典里的统计信息可能会很有帮助。比如全局临时表的统计信息。可以手动通过DBMS_STATS.SET_*_STATS来收集。

发表 xbdba Tuning13 分钟读完 (大约1983个字)

12.2统计信息之分区表

分区表上的统计信息要同时计算表级别和分区级别，11g以前新增一个分区或者修改少数分区的数据需要扫描整张表去刷新表的统计信息。如果跳过收集全局统计信息，那么优化器则会根据分区级别的统计信息去推断全局级别的统计信息。这种方法对于简单的表统计信息（例如行数）是准确的——通过聚合所有分区的行数，但是其他的统计信息就无法准确的推断。比如无法从每个分区独立的信息来准确推断出一个字段的唯一值记录（优化器使用的最重要统计信息之一）。

oracle 11g开始加强了这部分的统计信息收集手段，引入了增量全局统计信息。如果分区表的INCREMENTAL选项设置为true,DBMS_STATS.GATHER_*_STATS的GRANULARITY参数包含GLOBAL,并且ESTIMATE_PERCENT设置成AUTO_SAMPLE_SIZE,Oracle 会对新的分区收集信息，并且通过只扫描那些新增或进行修改了的分区从而准确的更新全局统计信息，这时就不需要扫描全表。

增量全局统计信息会对每个分区储存_概要_，_概要_表示分区和分区字段的元数据。每个概要都是储存在SYSAUX表空间。然后通过聚合每个分区级别的统计信息和分区概要来收集全局统计信息，因此就不需要扫描整张表来收集表级别统计信息。当一个新分区添加到表中，你只需要收集单个分区的统计信息，全局统计信息会自动维护和使用新分区的概要来准确更新数据。

注意INCREMENTAL统计信息并不适用子分区，子分区和分区上的信息收集就跟正常的一样。只有分区上的统计信息用于更新全局或表级别的信息时，增量才有用。下面是如何使用增量统计信息

开启增量统计信息

1
EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'INCREMENTAL', 'TRUE');

跟平常一样收集，ESTIMATE_PERCENT 和 GRANULARITY都是默认值

1
exec DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES');

检查当前INCREMENTAL值

1
SELECT DBMS_STATS.GET_PREFS('INCREMENTAL', 'SH', 'SALES') FROM dual;

增量统计信息和过期

11g时，如果一个表上启用了增量统计信息，当一个分区的一行更新后，那么这个分区上的统计信息就被认为是过期的，如果要生成全局信息则必须要重新收集这个分区的统计信息。

12c有一个新的选项INCREMENTAL_STALENESS,能让你去控制什么时候分区统计信息被认为是过期的或者已经不适合用来生成全局统计信息了。默认这个值是NULL,表示分区有值变更时就立刻认为是过期（跟11g一样）。

它也可以设成USE_STALE_PERCENT或者USE_LOCKED_STATS,USE_STALE_PERCENT，表示变化的行数所占百分比小于设置的STALE_PRECENTAGE值时（默认10%），分区级别的统计信息一直被认为可用。USE_LOCKED_STATS表示如果一个分区的统计信息被锁定了，那么它就可以被用来生成全局统计信息而不用考虑这个分区从上次收集统计信息到现在有多少行记录做了变更。

给表ORDER_DEMO设置USE_STALE_PERCENT

1
2
3
4
5
6
7
8
BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'INCREMENTAL_STALENESS', 
        pvalue   => 'USE_STALE_PERCENT');
END;
/

修改默认的10%到20%

1
2
3
4
5
6
7
8
BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'STALE_PERCENT', 
        pvalue   => 20);
END;
/

分区表统计信息锁定的情况

给表ORDER_DEMO设置USE_LOCKED_STATS

1
2
3
4
5
6
7
8
BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'INCREMENTAL_STALENESS', 
        pvalue   => 'USE_LOCKED_STATS');
END;
/

给表ORDER_DEMO同时设置USE_STALE_PERCENT和USE_LOCKED_STATS

1
2
3
4
5
6
7
8
BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'INCREMENTAL_STALENESS', 
        pvalue   => 'USE_STALE_PERCENT,USE_LOCKED_STATS');
END;
/

增量统计和分区加载交换

分区的一个好处就是能快速而方便的加载数据，通过exchange partition命令能最小化对用户的影响。exchange partition命令可以让非分区表的数据交换到分区表中的指定分区。这个命令不是物理移动数据，而是更新数据字典交换指针从分区到表，反之亦然。

在之前的版本当中，是无法在分区交换的过程中对非分区表生成必要的统计信息用于支持增量统计信息。而是必须在分区交换动作完成以后才能收集，为了确保全家统计信息能被增量维护。

在12c中，必要的统计信息（概要）可以在交换之前就创建在非分区表上，所以在分区交换的过程中统计信息也被交换用于自动维护增量的全家统计信息。新的DBMS_STATS表选项INCREMENTAL_LEVEL可以被用来去确定一个将要做加载交换的非分区表。INCREMENTAL_LEVEL设成TABLE时（默认PARTITION），oracle将在收集统计信息时自动为表创建概要。这个表级别的概要将在分区交换完毕后变成分区的概要。

1
2
exec dbms_stats.set_table_prefs ('SH','EXCHANGETAB','INCREMENTAL','TRUE');
exec dbms_stats.set_table_prefs ('SH','EXCHANGETAB','INCREMENTAL_LEVEL','TABLE');

概要增强

对于那些含有大量分区表的数据仓库应用来说，增量维护节省了大量的时间。然而带来了性能上的收益却也伴随这磁盘费用的增加：概要信息都是储存在SYSAUX表空间，需要越来越多的磁盘用来存储那些超多分区、超多字段、特别是那些字段唯一性特别高的表的概要。除了磁盘的消耗，你必须也要考虑到维护大量概要信息所带来的资源消耗。

从12.2开始，DBMS_STATS包提供了对于收集唯一值信息的新算法，在精确度与以前相似的条件下产生少的多的概要。

DBMS_STATS的新选项APPROXIMATE_NDV_ALGORITHM用来控制创建哪种概要。默认值是REPEAT OR HYPERLOGLOG，表示那些已存在的自适应采用算法将适用于已存在的概要，但是新概要都通过新的HYPERLOGLOG算法。在同一张表中可以混合新旧算法同时使用。

当将一个12.2之前的版本升级到12.2（使用增量信息）时，有三个选项：

1.继续使用12.2以前的格式

调整DBMS_STATS参数为adaptive sampling

1
EXEC dbms_stats.set_database_prefs ('approximate_ndv_algorithm', 'adaptive sampling');

2.立刻调整旧算法为新的

所有分区的统计信息会被重新收集

1
2
EXEC dbms_stats.set_database_prefs ('approximate_ndv_algorithm', 'hyperloglog');
EXEC dbms_stats.set_database_prefs ('incremental_staleness', NULL);

3.逐渐用新算法替换旧算法

旧的概要不会被立即删掉，新的分区会采用新算法格式的概要。混合模式可能会产生不太准确的统计信息，但是这样就不需要在前台重新收集所有统计信息，因为自动统计信息收集任务将重新收集具有旧概要的分区信息，并使用新格式。到最后所有的概要都会处于新的格式，统计信息也都会精确。

1
2
EXEC dbms_stats.set_database_prefs ('approximate_ndv_algorithm', 'hyperloglog');
EXEC dbms_stats.set_database_prefs ('incremental_staleness', 'allow_mixed_format');
发表 xbdba 安装/升级5 分钟读完 (大约816个字)

通过DBUA升级11g到12.2

12c已经出来了很长时间，公司的11g系统可能都考虑升级到12c，整理一下相关操作。这篇文章都是在实验环境上，所以有些不必要的步骤就没有一一做完，如果是生产环境则需要认真读一下官方文档Database Upgrade Guide 12.2

看一下升级到最新的12.2可以通过哪些途径

Current Release Upgrade Options
12.1.0.1, 12.1.0.2
11.2.0.3 or later		 可以直接升级,参考对应版本的升级手册即可
11.2.0.1, 11.2.0.2
11.1.0.6, 11.1.0.7
10.2.0.2, 10.2.0.3, 10.2.0.4 and 10.2.0.5
10.1.0.5
9.2.0.8 or earlier		 无法直接升级到12.2

解决办法：先升级到最新可以直接升级的版本。
例如现在是10.2.0.5则先升级到11.2.0.4，再从11.2.0.4升级到12.2

先决条件

执行升级过程之前，有大量的准备工作要检查，必须要对着文档仔细检查，保证升级过程的顺利完成。

具体参考前面所述文档的Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades部分

部分内容在后面的DBUA环节也可以勾选处理

搜集统计信息以减少停机时间

1
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

确认物化视图全部刷新完成

1
2
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
                   WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

确认没有处于备份模式的文件

1
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

确认没有文件需要恢复

1
SELECT * FROM v$recover_file;

处理未完成的分布式事物

1
2
3
4
5
SELECT * FROM dba_2pc_pending;

SELECT local_tran_id FROM dba_2pc_pending;
EXECUTE dbms_transaction.purge_lost_db_entry('');
commit;

确保主备同步

1
2
3
SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

清除回收站

1
PURGE DBA_RECYCLEBIN

手动删除DB Control

主要目的是为了减少停机时间

1
2
3
emctl stop dbconsole

@emremove.sql

emremove.sql执行完毕后，需要手动删除ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID两个目录

编译失效对象

1
@?/rdbms/admin/utlrp.sql

DBUA升级

最方便的办法就是通过DBUA工具，图形化界面也直观容易操作，本身就提供了一些必要的升级前检查，只要安装了数据库软件，工具就自带了。

我的环境已经装好了12c的库，所以直接切换到12c环境变量下，执行dbua

这里碰到个小问题，Pre-upgrade fails - SEVERE: For input string: “Us”，注意要unset ORACLE_PATH

选择要升级的库，下一步

都是一些warning 可以忽略

勾选一些升级前必要步骤

选择回退方案，我这里选择不备份

选择LISTENER，我是配置在11g里

EM配置

确认下信息，点击finish

upgrade完成以后，点击Upgrade Results


升级完毕的database现在已经可以使用了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[oracle@xb ~]$ export ORACLE_SID=test
[oracle@xb ~]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 11 14:45:49 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  test-test            xb.oracle.com             51    21129    12.2.0.1.0 20181211 9203       39    9202            000000006D32F050 000000006D5A76F8


sys@TEST> SELECT name, open_mode FROM v$database;

NAME                        OPEN_MODE
--------------------------- ------------------------------------------------------------
TEST                        READ WRITE
关注我

链接

分类

最新文章

归档

标签

12c15
18c1
Backup & Recovery7
Internal1
Patch1
asm1
awr1
bbed2
bug1
cbo3
cluster3
cursor1
dataguard4
datapump2
dbua1
dg broker1
dns1
emcc1
histograms1
internal2
json3
linux1
migration1
mmon1
multitenant3
ogg2
ora-8
partition5
pl/sql1
redefinition1
result cache1
rman3
scripts3
sql tuning1
ssh1
statistics5
xtts1
yum1
zabbix3
zabbix preprocessing1
×