查询中让优化器使用复合索引


create table MT_BIZ
(
  HOSPITAL_ID      VARCHAR2(20) not null,
  SERIAL_NO        VARCHAR2(16) not null,
  FEE_BATCH        NUMBER(5) default 1 not null,
  CASE_ID          NUMBER(12),
  BIZ_TYPE         VARCHAR2(2) not null,
  ORDINAL_NO       NUMBER(2) default 0 not null,
  DISTRICT_CODE    VARCHAR2(6),
  INDI_ID          NUMBER(12) not null,
  NAME             VARCHAR2(20) not null,
  SEX              CHAR(1) not null,
  PERS_TYPE        VARCHAR2(3) not null,
  OFFICE_GRADE     VARCHAR2(3) default '000' not null,
  IDCARD           VARCHAR2(25),
  IC_NO            VARCHAR2(25),
  BIRTHDAY         DATE,
  TELEPHONE        VARCHAR2(30),
  CORP_ID          NUMBER(10) not null,
  CORP_NAME        VARCHAR2(70) not null,
  TREATMENT_TYPE   VARCHAR2(3) not null,
  BIZ_TIMES        NUMBER(3),
  RELA_HOSP_ID     VARCHAR2(20),
  RELA_SERIAL_NO   VARCHAR2(16),
  SERIAL_APPLY     NUMBER(12),
  REG_DATE         DATE not null,
  REG_STAFF        VARCHAR2(8) not null,
  REG_MAN          VARCHAR2(20),
  REG_FLAG         CHAR(1) default '0' not null,
  BEGIN_DATE       DATE not null,
  REG_INFO         VARCHAR2(10),
  IN_DEPT          VARCHAR2(10),
  IN_DEPT_NAME     VARCHAR2(20),
  IN_AREA          VARCHAR2(10),
  IN_AREA_NAME     VARCHAR2(20),
  IN_BED           VARCHAR2(10),
  BED_TYPE         CHAR(1),
  PATIENT_ID       VARCHAR2(20),
  IN_DISEASE       VARCHAR2(20) not null,
  FOREGIFT         NUMBER(10,2),
  DIAGNOSE_DATE    DATE,
  DIAGNOSE         VARCHAR2(20),
  IN_DAYS          NUMBER(4),
  FIN_DISEASE      VARCHAR2(20),
  END_DATE         DATE,
  END_STAFF        VARCHAR2(8),
  END_MAN          VARCHAR2(30),
  FIN_INFO         VARCHAR2(10),
  IC_FLAG          CHAR(1) default '0' not null,
  REIMBURSE_FLAG   CHAR(1) default '0' not null,
  BIZ_END_DATE     DATE,
  FINISH_FLAG      CHAR(1) default '0' not null,
  POS_CODE         VARCHAR2(10),
  LOCK_FLAG        CHAR(1) default '0' not null,
  INJURY_BORTH_SN  NUMBER(12),
  REMARK           VARCHAR2(500),
  TRANS_FLAG       CHAR(1) default '0' not null,
  CENTER_ID        VARCHAR2(100) default 0 not null,
  PERS_TYPE_DETAIL VARCHAR2(3),
  CORP_TYPE_CODE   VARCHAR2(3),
  SPECIAL_CODE     VARCHAR2(3),
  DOCTOR_NO        VARCHAR2(20),
  DOCTOR_NAME      VARCHAR2(20),
  FIN_DISEASE1     VARCHAR2(20),
  FIN_DISEASE2     VARCHAR2(20),
  CASE_INFO        VARCHAR2(100),
  BILL_NO          VARCHAR2(20),
  HOS_SERIAL       VARCHAR2(30),
  DISEASE_TYPE     CHAR(1),
  DISEASE_FLAG     CHAR(1) default '0',
  RECUR_FLAG       CHAR(1) default '0',
  INJURY_TYPE      VARCHAR2(2) default '01'
);
-- Add comments to the table
comment on table MT_BIZ
  is '医疗业务表';
-- Add comments to the columns
comment on column MT_BIZ.HOSPITAL_ID
  is '医疗机构编号';
comment on column MT_BIZ.SERIAL_NO
  is '业务序列号';
comment on column MT_BIZ.FEE_BATCH
  is '费用批次';
comment on column MT_BIZ.CASE_ID
  is '病例分型序号';
comment on column MT_BIZ.BIZ_TYPE
  is '业务类别编号';
comment on column MT_BIZ.ORDINAL_NO
  is '内部序数';
comment on column MT_BIZ.DISTRICT_CODE
  is '社区编码(指个人所属行政区编码)';
comment on column MT_BIZ.INDI_ID
  is '个人编号';
comment on column MT_BIZ.NAME
  is '姓名';
comment on column MT_BIZ.SEX
  is '性别';
comment on column MT_BIZ.PERS_TYPE
  is '人员类别待遇代码';
comment on column MT_BIZ.OFFICE_GRADE
  is '公务员级别';
comment on column MT_BIZ.IDCARD
  is '公民身份号码';
comment on column MT_BIZ.IC_NO
  is 'IC卡号';
comment on column MT_BIZ.BIRTHDAY
  is '出生日期';
comment on column MT_BIZ.TELEPHONE
  is '联系电话';
comment on column MT_BIZ.CORP_ID
  is '单位编码';
comment on column MT_BIZ.CORP_NAME
  is '单位名称';
comment on column MT_BIZ.TREATMENT_TYPE
  is '待遇类别(用于区分同一业务类型的不同情况,比如生育门诊的三个月以上和三个月以上流产,不区分时为0)';
comment on column MT_BIZ.BIZ_TIMES
  is '本年业务次数';
comment on column MT_BIZ.RELA_HOSP_ID
  is '关联医疗机构编码';
comment on column MT_BIZ.RELA_SERIAL_NO
  is '关联业务序列号';
comment on column MT_BIZ.SERIAL_APPLY
  is '申请序列号';
comment on column MT_BIZ.REG_DATE
  is '业务登记日期';
comment on column MT_BIZ.REG_STAFF
  is '登记人工号';
comment on column MT_BIZ.REG_MAN
  is '登记人';
comment on column MT_BIZ.REG_FLAG
  is '登记标志(0:正常 1:转院 2:二次返院(审批通过后RELA_SERIAL_NO为空) 3:急诊留观转住院 4:90天或180天结算(处理后RELA_HOSP_ID为空,RELA_SERIAL_NO不为空))';
comment on column MT_BIZ.BEGIN_DATE
  is '业务开始时间';
comment on column MT_BIZ.REG_INFO
  is '业务开始情况(FR:提取冻结费用的零报业务  MW:医疗转工伤的零报业务)';
comment on column MT_BIZ.IN_DEPT
  is '入院科室';
comment on column MT_BIZ.IN_DEPT_NAME
  is '入院科室名称';
comment on column MT_BIZ.IN_AREA
  is '入院病区';
comment on column MT_BIZ.IN_AREA_NAME
  is '入院病区名称';
comment on column MT_BIZ.IN_BED
  is '入院床位号';
comment on column MT_BIZ.BED_TYPE
  is '床位类型';
comment on column MT_BIZ.PATIENT_ID
  is '医院业务号';
comment on column MT_BIZ.IN_DISEASE
  is '入院疾病诊断';
comment on column MT_BIZ.FOREGIFT
  is '预付款总额';
comment on column MT_BIZ.DIAGNOSE_DATE
  is '确诊日期';
comment on column MT_BIZ.DIAGNOSE
  is '确诊疾病诊断';
comment on column MT_BIZ.IN_DAYS
  is '住院天数';
comment on column MT_BIZ.FIN_DISEASE
  is '出院疾病诊断';
comment on column MT_BIZ.END_DATE
  is '业务终结日期';
comment on column MT_BIZ.END_STAFF
  is '终结人工号';
comment on column MT_BIZ.END_MAN
  is '终结人';
comment on column MT_BIZ.FIN_INFO
  is '业务终结情况';
comment on column MT_BIZ.IC_FLAG
  is '用卡标志';
comment on column MT_BIZ.REIMBURSE_FLAG
  is '中心报帐标志';
comment on column MT_BIZ.BIZ_END_DATE
  is '诊次结束时间';
comment on column MT_BIZ.FINISH_FLAG
  is '完成标志';
comment on column MT_BIZ.POS_CODE
  is 'POS机编号';
comment on column MT_BIZ.LOCK_FLAG
  is '锁定标志';
comment on column MT_BIZ.INJURY_BORTH_SN
  is '对应的工伤生育业务号';
comment on column MT_BIZ.REMARK
  is '备注';
comment on column MT_BIZ.TRANS_FLAG
  is '传输标志(0:未传输 1:已成功传输 2:未成功传输)';
comment on column MT_BIZ.CENTER_ID
  is '医保中心编码';
comment on column MT_BIZ.PERS_TYPE_DETAIL
  is '人员类别详细代码(bs_person.PERS_TYPE)';
comment on column MT_BIZ.CORP_TYPE_CODE
  is '单位类型';
comment on column MT_BIZ.SPECIAL_CODE
  is '特殊人群编码';
comment on column MT_BIZ.DOCTOR_NO
  is '医生编号';
comment on column MT_BIZ.DOCTOR_NAME
  is '医生姓名';
comment on column MT_BIZ.FIN_DISEASE1
  is '第一副诊断';
comment on column MT_BIZ.FIN_DISEASE2
  is '第二副诊断';
comment on column MT_BIZ.CASE_INFO
  is '病历信息';
comment on column MT_BIZ.BILL_NO
  is '单据号';
comment on column MT_BIZ.HOS_SERIAL
  is '医院交易流水号';
comment on column MT_BIZ.DISEASE_TYPE
  is '病种分型(A:病种单纯 B:严重 C:严重并发 D:危重)';
comment on column MT_BIZ.DISEASE_FLAG
  is '职业病标志(0 不是职业病,1 是职业病)';
comment on column MT_BIZ.RECUR_FLAG
  is '工伤复发标志(0 不是工伤复发,1 是工伤复发)';
comment on column MT_BIZ.INJURY_TYPE
  is '工伤类别(01:新工伤,02老工伤,对应新增wi_injury_type码表)';
-- Create/Recreate primary, unique and foreign key constraints
alter table MT_BIZ
  add constraint PK_MT_BIZ primary key (HOSPITAL_ID, SERIAL_NO)
  using index;
-- Create/Recreate indexes
create index IDX_MT_BIZ_BEGIN_DATE on MT_BIZ (BEGIN_DATE);
create index IDX_MT_BIZ_BIZ_END_DATE on MT_BIZ (BIZ_END_DATE);
create index IDX_MT_BIZ_CORP_ID on MT_BIZ (CORP_ID);
create index IDX_MT_BIZ_IB on MT_BIZ (INJURY_BORTH_SN);
create index IDX_MT_BIZ_INDI_ID on MT_BIZ (INDI_ID);
create index IDX_MT_BIZ_RELA on MT_BIZ (RELA_HOSP_ID, RELA_SERIAL_NO);

在mt_biz表中有一个复合主键是由hospital_id与serial_no组成的
其中serial_no是唯一值是由序列生成的,所以在查询数据时有些语句只使用serial_no
造成了使用不上索引的问题
因为如果索引是建立在多个列上, 只有在它的第一个列也叫前导列(leading
column)被where子句引用时,优化器才会选择使用该索引.
例如,不使用合主键的唯一索引中的前导列hospital_id时的语句执行计划如下

SQL> set autotrace traceonly;
SQL> select * from mt_biz a where a.serial_no='15485197';


执行计划
----------------------------------------------------------
Plan hash value: 3513793642

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |   244 |    42   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MT_BIZ |     1 |   244 |    42   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."SERIAL_NO"='15485197')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        190  consistent gets
          0  physical reads
          0  redo size
       2852  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

从显示的执行计划可以看出当没有使用复合索引中的前导列hospital_id时
是执行的全表扫描

当使用复合索引中的前导列hospital_id时

SQL> select * from mt_biz a where a.hospital_id='4307000009';

已选择348行。


执行计划
----------------------------------------------------------
Plan hash value: 3033165289

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |    18 |  4392 |     8   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MT_BIZ    |    18 |  4392 |     8   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | PK_MT_BIZ |    18 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."HOSPITAL_ID"='4307000009')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        345  consistent gets
          0  physical reads
          0  redo size
     102775  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
         25  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        348  rows processed

给出的执行计划是通过INDEX RANGE SCAN来执行查询

当使用复合索引中所有列时

SQL> select * from mt_biz a where a.hospital_id='4307000009' and a.serial_no='15485197';


执行计划
----------------------------------------------------------
Plan hash value: 2316229530

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     1 |   244 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MT_BIZ    |     1 |   244 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_MT_BIZ |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."HOSPITAL_ID"='4307000009' AND "A"."SERIAL_NO"='15485197')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       2818  bytes sent via SQL*Net to client
        232  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

给出的执行计划是使用INDEX UNIQUE SCAN索引扫描来查询
所以当创建复合索引后如果查询要想使用这个复合索引就必须在查询条件中
使用复合索引的前导列才会让优化器使用这个复合索引

回闪查询查询删除的数据

oracle回闪技术:代替按时间点恢复
oracle回闪技术提供了一组功能它有效的代替了查看数据过去的状态和数据修改前后的状态而不用要你从备份中还原大部分数据或执行按时间点恢复.回闪技术与介质恢复相比更有效和更小的损坏数据.
大多数据回闪技术功能是在逻辑级别的操作,象查看和维护数据库对象
回闪查询:让你指定一个目标时间然后运行查询,来查看在那个时间出现的查询结查.为了从一个不想要发生的改变象用户错误的更新一个,用户可以选择在出错前的某个时间点运行查询来检索丢失或改变的记录.

回闪版本查询:让你可以查询在一个指定时间间隔内一个表或多个表永久存的所有记录的所有版本.象在表上执行的所有更新.也可能检索行记录不同版本的元数据象开始时间,结束时间,进行的操作和这个事务的事务ID创建的版本.这个功能也用来恢复丢失的数据和查询审计表的改变.

回闪事务查询:让你可能查看由单个事务发生的改变或在一个时间周期内所有事务发生的改变.

回闪表:能让你将表还原到过去某一个时间点.你能在数据库联机的情况下还原表数据,而仅仅回滚你指定的表的数据.

回闪删除:能拆消drop table语句的影响

回闪表,回闪查询,回闪事务查询和回闪版本查询都是要依赖重做数据.重做数据是用来记录数据库中每一个更新操作的数据的.使用它的主要目的是用来给查询提供一至性读取和回滚事务。

回闪删除是建交在一种叫做回收站机制之上的,oracle使用它来管理删除的数据库对象,直到回收站空间不足以要给新对象分配空间时才会清除.
查询一个表的过去状态可以使用select as of子句来完成.

select *
 from lv_insr_topay
    as of timestamp (sysdate - 40/1440)
    where corp_id=777;--删除前

    select *
 from lv_insr_topay
    as of timestamp (sysdate - 10/1440)
    where corp_id=777;--删除后


select *
 from lv_insr_topay
    as of timestamp to_timestamp('2011-12-26 15:00:00','yyyy-mm-dd hh24:mi:ss')
    where corp_id=777;--删除前

    select *
 from lv_insr_topay
    as of timestamp to_timestamp('2011-12-26 15:09:00','yyyy-mm-dd hh24:mi:ss')
    where corp_id=777;--删除后


select count(*) from zw2004.gl_pznr versions
  between  timestamp to_timestamp('2012-01-15 10:40:01','yyyy-mm-dd hh24:mi:ss') and
 to_timestamp('2012-01-17 09:50:00','yyyy-mm-dd hh24:mi:ss');

回闪版本查询

select versions_starttime,versions_endtime, versions_xid,versions_operation,POLICY_CODE,POLICY_VALUE,CENTER_OR_HOSP from fc_biz_policy  versions  between  timestamp to_timestamp('2012-04-19 10:40:01','yyyy-mm-dd hh24:mi:ss') and
 to_timestamp('2012-04-22 09:50:00','yyyy-mm-dd hh24:mi:ss') where policy_code='use_IC'

回闪事务查询

select * from flashback_transaction_query a where a.table_owner='ZW2004' and a.table_name='GL_PZNR'

回闪删除掉的表

flashback table jhk_map_center to before drop

data gurad物理备份方式下重命名数据文件

重命名数据文件

如果primary 数据库重命令了一个或多个数据文件,该项修改并不会自动传播到standby 数据库。
如果你想让standby 和数据文件与primary 保持一致,那你也只能自己手工操作了。就算STANDBY_FILE_MANAGEMENT 也帮不上忙啦,不管它是auto 还是manual。
下面通过示例做个演示:
A).将重命名的数据文件所在表空间offline –primary 数据库操作

SQL> alter tablespace users offline;

Tablespace altered.

B).手工将数据文件改名(操作系统) –primary 数据库操作

C).通过命令修改数据字典中的数据文件路径,并online 表空间–primary 数据库操作

SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/jytest/users01.dbf' to '/u01/app/oracle/oradata/jytest/myusers01.dbf';

Tablespace altered.


SQL> alter tablespace users online;

Tablespace altered.

D).暂停redo 应用,并shutdown –standby 数据库操作

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

E).手工将数据文件改名(操作系统) –standby 数据库操作

F).重启standby,修改数据文件路径(数据字典) –standby 数据库操作

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2030368 bytes
Variable Size            1090520288 bytes
Database Buffers         5335154688 bytes
Redo Buffers               14745600 bytes
Database mounted.

SQL> alter database rename file
2 '/u01/app/oracle/oradata/jytest/users01.dbf' to
3 '/u01/app/oracle/oradata/jytest/myusers01.dbf';
表空间已更改

G).重新启动redo 应用。

SQL> alter system set standby_file_management='MANUAL' scope=both;

System altered.

SQL> alter database rename file '/u01/app/oracle/oradata/jytest/users01.dbf' to  '/u01/app/oracle/oradata/jytest/myusers01.dbf';

Database altered.

SQL> alter system set standby_file_management='AUTO' scope=both;

System altered.

H).切换日志–primary 数据库操作

SQL> alter system switch logfile;
系统已更改。

data gurad物理备份方式下standby_file_management为manual时修改表空间的操作

STANDBY_FILE_MANAGEMENT设置为MANUAL,增加及删除表空间和数据文件

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> alter system set standby_file_management='MANUAL' scope=both;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

A).增加新的表空间–primary 数据库操作

SQL>CREATE  TABLESPACE mytest DATAFILE '/u01/app/oracle/oradata/jytest/mytest01.dbf' size 20M

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/oradata/jytest/mytest01.dbf

6 rows selected

切换日志

SQL> alter system switch logfile;

System altered

SQL>

B).验证standby 库–standby 数据库操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/product/10.2.0/db/dbs/UNNAMED00006

6 rows selected.

SQL>


SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST

7 rows selected.

可以看到,表空间已经自动创建,但是,数据文件却被起了个怪名字,手工修改其与primary
数据库保持一致.

SQL>alter database create datafile
'/u01/app/oracle/product/10.2.0/db/dbs/UNNAMED00006'
as '/u01/app/oracle/oradata/jytest/mytest01.dbf';

C).删除表空间–primary 数据库操作

SQL> drop tablespace mytest including contents and datafiles;

Tablespace dropped

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf

SQL> alter system switch logfile;

System altered

D).验证standby 数据库–standby 数据库操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/oradata/jytest/mytest01.dbf

6 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST

7 rows selected.

数据还在啊。查看alertjytest.log 文件,发现如下
MRP0: Background Media Recovery terminated with error 1274
Mon Dec 3 17:03:34 2012
重启redo 应用再来看看:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf

SQL>  select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA

6 rows selected.

注意,既使你在primary 数据库执行删除时加上了including 子句,在standby 数据库仍然只会
将表空间和数据文件从数据字典中删除,你还需要手工删除表空间涉及的数据文件。

data gurad物理备份方式下standby_file_management为auto时修改表空间的操作

STANDBY_FILE_MANAGEMENT设置为AUTO

增加及删除表空间和数据文件
我们先来看看初始化参数的设置: —-standby 数据库操作

SQL> show parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

A).增加新的表空间–primary 数据库操作

SQL>CREATE TABLESPACE mytest DATAFILE '/u01/app/oracle/oradata/jytest/mytest01.dbf' size 20M
表空间已创建。

检查刚添加的数据文件

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/oradata/jytest/mytest01.dbf

6 rows selected

SQL>

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST

7 rows selected

切换日志

SQL> alter system switch logfile;
系统已更改。

B).验证standby 库–standby 数据库操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/oradata/jytest/mytest01.dbf

6 rows selected.

SQL>




SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST

7 rows selected.

可以看到,表空间和数据文件已经自动创建,你是不是奇怪为什么数据文件路径自动变成了jytest(因为我这里是主备不在同一台机器上且数据库结构目录相同),因为我们设置了db_file_name_convert 嘛。

C).删除表空间–primary 数据库操作

SQL> drop tablespace mytest including contents and datafiles;

Tablespace dropped

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf

SQL> alter system switch logfile;

System altered

提示:使用including 子句删除表空间时,
D).验证standby 数据库–standby 数据库操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf

SQL>

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA

6 rows selected.

得出结论,对于初始化参数STANDBY_FILE_MANAGMENT 设置为auto 的话,对于表空间和数
据文件的操作完全无须dba 手工干预,primary 和standby 都能很好的处理

data gurad物理备份方式下以READ ONLY/WRITE模式打开物理STANDBY

一、READONLY/WRITE模式打开物理STANDBY

物理standby可以有效分担primary 数据库压力,提升资源利用,实际上说的就是这个。以read
only 或read write 模式打开物理standby,你可以转移一些查询任何啦,
备份之类的操作到standby 数据库,以这种方式来分担一些primary 的压力。
下面我们来演示一下,如何切换standby 数据库的打开模式,其实,非常
简单。例如,以Read-only 模式打开物理standby:
这里要分两种情况:
1).standby 数据库处于shutdown 状态
直接startup 即可。

SQL> startup
ORACLE 例程已经启动。
......

2).standby 数据库处于redo 应用状态。
首先取消redo 应用:

SQL> alter database recover managed standby database cancel;
数据库已更改。

然后再打开数据库

SQL> alter database open ;

数据库已更改。
提示:open 的时候不需要附加read only 子句,oracle 会根据控制文件判断是否是物理standby,从而自
动启动到read only 模式,直接startup 也是同理。

3).如果想从open 状态再切换回redo 应用状态,可以直接启用redo 应用即可,例如:

SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。

SQL> select status from v$instance;
STATUS
------------
MOUNTED

有时你也可能也要先shutdown再startup mount后再执行

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2030368 bytes
Variable Size            1090520288 bytes
Database Buffers         5335154688 bytes
Redo Buffers               14745600 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

data gurad物理备份方式中的failover转换

切换分为switchover和failover,前者是无损切换,不会丢失数据,而后者则有可能会丢失数据,并且切换后原primary 数据库也不再是该data guard 配置的一部分了.针对不同standby(逻辑或物理)的处理方式也不尽相同

角色转换前的准备工作
检查各数据库的初始化参数,主要确认对不同角色相关的初始化参数都进行了正确的配置。
确保可能成为primary 数据库的standby 服务器已经处于archivelog 模式。
确保standby 数据库的临时文件存在并匹配primary 数据库的临时文件
确保standby 数据库的RAC 实例只有一个处于open 状态。(对于rac 结构的standby 数据库,在角
色转换时只能有一个实例startup。其它rac 实例必须统统shutdown,待角色转换结束后再startup)

Switchover:
无损转换,通常是用户手动触发或者有计划的让其自动触发,比如硬件升级啦,软件升级啦之类的。
通常它给你带来的工作量非常小并且都是可预计的。其执行分两个阶段,第一步, primary 数据库转换为
standby 角色,第二步,standby 数据库(之一)转换为primary 角色,primary 和standby 只是简单的角色互换.
Failover:
不可预知原因导致primary 数据库故障并且短期内不能恢复就需要failover。
在执行failover 之前,尽可能将原primary 数据库的可用redo 都复制到standby 数据库。
注意,如果要转换角色的standby 处于maximum protection 模式,需要你首先将其切换为maximum
performance模式.转换standby 数据库到MAXIMIZE PERFORMANCE 执行下列SQL 即可:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
等standby 切换为新的primary 之后,你可以再随意更改数据库的保护模式。
maximum protection 模式需要确保绝无数据丢失,因此其对于提交事务对应的redo 数据一致性要求非常高,
另外,如果处于maximum protection 模式下primary 数据库仍然与standby 数据库有数据传输,此时alter
database 语句更改standby 数据库保护模式会失败,这也是由maximum protection 模式特性决定的。

下面演示failover的过程:
一物理standby的failover
注意几点:
failover 之后,原primary 数据库默认不再是data guard 配置的一部分。
多数情况下,其它逻辑/物理standby 数据库不直接参与failover 的过程,
因此这些数据库不需要做任何操作。
某些情况下,新的primary 数据库配置之后,需要重新创建其它所有的standby 数据库。
另外,如果待转换角色的standby 处于maximum protection 或maximum availability 模式的话,
归档日志应该是连续存在的.

一般情况下failover 都是表示primary 数据库瘫痪,因此这种类型的切换基本上不需
要primary数据库做什么操作。
1、检查归档文件是否连续
查询待转换standby 数据库的V$ARCHIVE_GAP 视图,确认归档文件是否连接:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
未选定行

如果返回的有记录,按照列出的记录号复制对应的归档文件到待转换的standby 服务器。这一步非常重
要,必须确保所有已生成的归档文件均已存在于standby 服务器,不然可能会数据不一致造成转换时报错。
文件复制之后,通过下列命令将其加入数据字典:

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filejytest1';

2、检查归档文件是否完整
分别在primary/standby 执行下列语句:

SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

该语句取得当前数据库各线程已归档文件最大序号,如果primary 与standby 最大序号不相同,必须将
多出的序号对应的归档文件复制到待转换的standby 服务器。不过既然是failover,有可能primary 数据库此时已经无法打开,甚至无法访问.
3、启动failover执行下列语句:

SQL> alter database recover managed standby database finish force;
数据库已更改。

FORCE 关键字将会停止当前活动的RFS 进程,以便立刻执行failover。
剩下的步骤就与前面switchover 很相似了
4、切换物理standby 角色为primary

SQL> alter database commit to switchover to primary;
数据库已更改。

5、启动新的primary 数据库。
如果当前数据库已mount,直接open 即可,如果处于read-only 模式,需要首先shutdown immediate,然
后再直接startup。

SQL> alter database open;
数据库已更改

角色转换工作完成。剩下的是补救措施(针对原primary 数据库),由于此时primary 数据库已经不再是
data guard配置的一部分,我们需要做的就是尝试看看能否恢复原primary数据库,将其改造为新的standby
服务器。

data guard物理备份方式中的switchover转换

切换分为switchover和failover,前者是无损切换,不会丢失数据,而后者则有可能会丢失数据,并且切换后原primary 数据库也不再是该data guard 配置的一部分了.针对不同standby(逻辑或物理)的处理方式也不尽相同

角色转换前的准备工作
检查各数据库的初始化参数,主要确认对不同角色相关的初始化参数都进行了正确的配置。
确保可能成为primary 数据库的standby 服务器已经处于archivelog 模式。
确保standby 数据库的临时文件存在并匹配primary 数据库的临时文件
确保standby 数据库的RAC 实例只有一个处于open 状态。(对于rac 结构的standby 数据库,在角
色转换时只能有一个实例startup。其它rac 实例必须统统shutdown,待角色转换结束后再startup)

Switchover:
无损转换,通常是用户手动触发或者有计划的让其自动触发,比如硬件升级啦,软件升级啦之类的。
通常它给你带来的工作量非常小并且都是可预计的。其执行分两个阶段,第一步, primary 数据库转换为
standby 角色,第二步,standby 数据库(之一)转换为primary 角色,primary 和standby 只是简单的角色互换.
Failover:
不可预知原因导致primary 数据库故障并且短期内不能恢复就需要failover。
在执行failover 之前,尽可能将原primary 数据库的可用redo 都复制到standby 数据库。
注意,如果要转换角色的standby 处于maximum protection 模式,需要你首先将其切换为maximum
performance模式.转换standby 数据库到MAXIMIZE PERFORMANCE 执行下列SQL 即可:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
等standby 切换为新的primary 之后,你可以再随意更改数据库的保护模式。
maximum protection 模式需要确保绝无数据丢失,因此其对于提交事务对应的redo 数据一致性要求非常高,
另外,如果处于maximum protection 模式下primary 数据库仍然与standby 数据库有数据传输,此时alter
database 语句更改standby 数据库保护模式会失败,这也是由maximum protection 模式特性决定的。

下面演示switchover的过程:
一、物理standby的Switchover
注意操作步骤的先后,很关键的哟。
1、检查是否支持switchover 操作–primary 数据库操作

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY

在第一次switch的时候看primary上的switchover_status字段上的值是session active。standby上面的该字段的值是not allowed,这个第一次没关系:

NOT ALLOWED - Either this is a standby database and the primary database has
not been switched first, or this is a primary database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to
the primary or standby database that need to be disconnected before the
switchover operation is permitted.

SWITCHOVER PENDING - This is a standby database and the primary database
switchover request has been received but not processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not complete
and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is
allowed to switch over to a primary database.

TO STANDBY - This is a primary database, with no active sessions, that is
allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the
switchover request.

During normal operations it is acceptable to see the following values for
SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY.
During normal operations on the standby it is acceptable to see the values
of NOT ALLOWED or SESSIONS ACTIVE.

2、启动switchover –primary 数据库操作
说明:
主库需要注意事项
A 如果switchover_status为TO_STANDBY说明可以转换
直接转换

alter database commit to switchover to physical standby;

B 如果switchover_status为SESSIONS ACTIVE 则关闭会话

SQL>alter database commit to switchover to physical standby with session shutdown;

在备库中操作,查看备库

SQL> select switchover_status from v$database;

A 如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库

SQL>alter database commit to switchover to primary

B 如果switchover_status为SESSION ACTIVE 就应该断开活动会话

SQL>alter database commit to switchover to primary with session shutdown;

C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到,此时不能
执行转换。

首先将primary 转换为standby 的角色,通过下列语句:

[oracle@weblogic28 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 3 10:09:33 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

语句执行完毕后,primary 数据库将会转换为standby 数据库,并自动备份控制文件到trace。

3、重启动到mount –原primary 数据库操作

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2030368 bytes
Variable Size            1090520288 bytes
Database Buffers         5335154688 bytes
Redo Buffers               14745600 bytes
Database mounted.

4、检查是否支持switchover 操作–待转换standby 数据库操作
待原primary 切换为standby 角色之后,检查待转换的standby 数据库switchover_status 列,看看是否支持角色转换。

[oracle@weblogic29 ~]$ sqlplsu /as sysdba
-bash: sqlplsu: command not found
[oracle@weblogic29 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 3 10:09:24 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

此时待转换standby 数据库switchover_status 列值应该是”TO_PRIMARY”,如否则检查其初始化参数文件中的设置,提示一下,比着原primary 数据库的初始化参数改改。

5、转换角色到primary –待转换standby 数据库操作
通过下列语句转换standby 到primary 角色:

SQL> alter database commit to switchover to primary;

Database altered.

注意:待转换的物理standby 可以处于mount 模式或open read only 模式,但不能处于open read write模式。

6、完成转换,打开新的primary 数据库

SQL> alter database open;

Database altered.

注:如果数据库处于open read-only 模式的话,需要先shutdown 然后直接startup 即可。

7、验证一下
新的primary 数据库

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      jyrac1

SQL> alter  system  switch  logfile;

System altered

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           140

新的standby 数据库

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      jytest
SQL>

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           140

转换成功。

SQL> alter database recover managed standby database disconnect from session;

data guard中增加与删除主备数据库中的联机重做日志与备重做日志文件

原主备数据库中的联机重做日志有3组备重做日志有4组,现在各增加一组

主库操作

1.1 查看redo 信息

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

---------- ------- --------------------------------------------------------------------------------

         3 ONLINE  /u01/app/oracle/oradata/jytest/redo03.log

         2 ONLINE  /u01/app/oracle/oradata/jytest/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/jytest/redo01.log

         4 STANDBY /u01/app/oracle/oradata/jytest/redo04a.log

         4 STANDBY /u01/app/oracle/oradata/jytest/redo04b.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05a.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05b.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06a.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06b.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07a.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07b.log

11 rows selected

 

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      INACTIVE                      50

         2          1 NO       CURRENT                       50

         3          1 YES      INACTIVE                      50

SQL>

 

1.2 修改standby redo

SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 4;

Database altered

SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 5;

Database altered

SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 6;

Database altered

SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 7;

Database altered

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

---------- ------- --------------------------------------------------------------------------------

         3 ONLINE  /u01/app/oracle/oradata/jytest/redo03.log

        2 ONLINE  /u01/app/oracle/oradata/jytest/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/jytest/redo01.log

添加standby redo

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE50 M;

Database altered

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

---------- ------- --------------------------------------------------------------------------------

         3 ONLINE  /u01/app/oracle/oradata/jytest/redo03.log

        2 ONLINE  /u01/app/oracle/oradata/jytest/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/jytest/redo01.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07.log

         8 STANDBY /u01/app/oracle/oradata/jytest/redo08.log

9 STANDBY /u01/app/oracle/oradata/jytest/redo09.log

8 rows selected

1.3 修改Online redo

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      INACTIVE                      50

         2          1 NO       CURRENT                       50

         3          1 YES      INACTIVE                      50

先处理inactive, 它表示已经完成规定的,可以删除。

但要记住必须要保留两组联机重做日志组

SQL> alter database drop logfile group 1;

Database altered

手工的把物理文件删除后,在创建:

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/jytest/redo01.log')SIZE50 M;

Database altered

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      UNUSED                        50

         2          1 NO       CURRENT                       50

         3          1 YES      INACTIVE                      50

group1 搞定了。

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/jytest/redo03.log')SIZE50 M;

Database altered

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      UNUSED                        50

         2          1 NO       CURRENT                       50

         3          1 YES      UNUSED                        50

Group3 搞定了。

切换一下logfile,在删除group2

SQL> alter system switch logfile;

System altered

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 NO       CURRENT                       50

         2          1 YES      ACTIVE                        50

         3          1 YES      UNUSED                        50

上面group2正在归档

几分钟之后:

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 NO       CURRENT                       50

         2          1 YES      INACTIVE                      50

         3          1 YES      UNUSED                        50

SQL>

SQL> alter database drop logfile group 2;

Database altered

手工的把物理文件删除后,在创建:

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/jytest/redo02.log')SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE50 M;

Database altered

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      INACTIVE                      50

         2          1 YES      INACTIVE                      50

         3          1 NO       CURRENT                       50

         4          1 YES      UNUSED                        50

主数据库的日志文件增加与删除操作就完成了

备库操作

2.1 查看日志信息

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

---------- ------- --------------------------------------------------------------------------------

         3 ONLINE  /u01/app/oracle/oradata/jytest/redo03.log

         2 ONLINE  /u01/app/oracle/oradata/jytest/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/jytest/redo01.log

         4 STANDBY /u01/app/oracle/oradata/jytest/redo04a.log

         4 STANDBY /u01/app/oracle/oradata/jytest/redo04b.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05a.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05b.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06a.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06b.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07a.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07b.log

11 rows selected

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      CLEARING                      50

         3          1 YES      CLEARING_CURRENT              50

         2          1 YES      CLEARING                      50

2.2 处理standby redo

对于standby 上redo的处理之前,我们要先停掉redo 的apply:

SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 4;

ALTER DATABASE drop STANDBY LOGFILE GROUP 4

ORA-00261: log 4 of thread 1 is being archived or modified

ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/jytest/redo04a.log'

ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/jytest/redo04b.log'

SQL> select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES       USED ARCHIVED STATUS     FIRST_CHANGE# FIRST_TIME  LAST_CHANGE# LAST_TIME

---------- ---------------------------------------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ -----------

         4 3836176504                                        1        182   52428800    3580928 YES      ACTIVE           1236181 2012-12-4 1      1238785 2012-12-4 1

显示group 4 status为active

SQL> alter database clear  logfile group 4;

Database altered

SQL> select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES       USED ARCHIVED STATUS     FIRST_CHANGE# FIRST_TIME  LAST_CHANGE# LAST_TIME

---------- ---------------------------------------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ -----------

         4 UNASSIGNED                                        1          0   52428800          0 YES      UNASSIGNED       1236181 2012-12-4 1      1239074 2012-12-4 1

SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 4;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE50 M;

Database altered

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

---------- ------- --------------------------------------------------------------------------------

         3 ONLINE  /u01/app/oracle/oradata/jytest/redo03.log

        2 ONLINE  /u01/app/oracle/oradata/jytest/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/jytest/redo01.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07.log

         8 STANDBY /u01/app/oracle/oradata/jytest/redo08.log

         9 STANDBY /u01/app/oracle/oradata/jytest/redo09.log

8 rows selected

2.3 处理online redo

先将standby_file_management设为手动:

SQL> alter system set standby_file_management='MANUAL' ;

System altered.

SQL> SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS

---------- ----------------

         1 CLEARING

         3 CLEARING_CURRENT

         2 CLEARING

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/jytest/redo01.log')SIZE50 M;

Database altered

SQL> alter database clear logfile group 2;

Database altered

SQL> alter database drop logfile group 2;

Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/jytest/redo02.log')SIZE50 M;

Database altered

SQL> SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         3 CLEARING_CURRENT

         2 UNUSED

还有最后一个redo 组没有处理,这个要先切换过来:

(1)在备库启动recover 进程:

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

 

System altered.

 

(2)到主库手动切换几次redo

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

在查看备库的redo:

SQL> SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         3 CLEARING

         2 CLEARING_CURRENT

原来group3已经变成clearing了

SQL> alter database clear logfile group 3;

Database altered

SQL> alter database drop logfile group 3;

Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/jytest/redo03.log')SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE50 M;

Database altered

查看:

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

    GROUP#    THREAD# ARCHIVED STATUS           BYTES/1024/1024

---------- ---------- -------- ---------------- ---------------

         1          1 YES      UNUSED                        50

         4          1 YES      UNUSED                        50

         3          1 YES      UNUSED                        50

         2          1 YES      CLEARING_CURRENT              50

SQL> select group#,type, member from v$logfile;

    GROUP# TYPE    MEMBER

---------- ------- --------------------------------------------------------------------------------

         3 ONLINE  /u01/app/oracle/oradata/jytest/redo03.log

         2 ONLINE  /u01/app/oracle/oradata/jytest/redo02.log

         1 ONLINE  /u01/app/oracle/oradata/jytest/redo01.log

         5 STANDBY /u01/app/oracle/oradata/jytest/redo05.log

         6 STANDBY /u01/app/oracle/oradata/jytest/redo06.log

         7 STANDBY /u01/app/oracle/oradata/jytest/redo07.log

         8 STANDBY /u01/app/oracle/oradata/jytest/redo08.log

         9 STANDBY /u01/app/oracle/oradata/jytest/redo09.log

         4 ONLINE  /u01/app/oracle/oradata/jytest/redo04.log

9 rows selected

搞定,最后启动recover,验证:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

 

System altered.

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

主库:

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

 

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           185

备库:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           185

同步了

oracle10g data guard 主备数据库配置参数说明

oracle10g data guard 主备数据库配置参数说明:

下列参数为primary角色相关的初始化参数:
DB_NAME 注意保持同一个Data Guard中所有数据库DB_NAME相同。

例如:DB_NAME=jytest

DB_UNIQUE_NAME 为每一个数据库指定一个唯一的名称,该参数一经指定不会再发生变化,

除非你主动修改它。

例如:DB_UNIQUE_NAME=jytest

LOG_ARCHIVE_CONFIG 该参数通过DG_CONFIG属性罗列同一个Data Guard中所有

DB_UNIQUE_NAME(含primary db及standby db),以逗号分隔

例如:LOG_ARCHIVE_CONFIG=’DB_CONFIG=(jytest,jyrac1)’

CONTROL_FILES 没啥说的,控制文件所在路径。
LOG_ARCHIVE_DEST_n 归档文件的生成路径。该参数非常重要,并且属性和子参数也特别多(这里

不一一列举,后面用到时单独讲解如果你黑好奇,建议直接查询oracle

方文档。Data guard白皮书第14章专门介绍了该参数各属性及子参数的功

能和设置)。例如:

LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/arch/jytest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jytest’

LOG_ARCHIVE_DEST_STATE_n 指定参数值为ENABLE,允许redo传输服务传输redo数据到指定的路径。

该参数共拥有4个属性值,功能各不相同。

REMOTE_LOGIN_PASSWORDFILE 推荐设置参数值为EXCLUSIVE或者SHARED,注意保证相同Data Guard

配置中所有db服务器sys密码相同。

LOG_ARCHIVE_FORMAT 指定归档文件格式。
LOG_ARCHIVE_MAX_PRODUCESSES 指定归档进程的数量(1-30),默认值通常是4。
以下参数为standby角色相关的参数,建议在Primary数据库的初始化参数中也进行设置,这样在role transition

后(Primary转为Standby)也能正常运行:

FAL_SERVER 指定一个数据库的DB_UNIQUE_NAME,通常该库为primary角色。

例如:FAL_SERVER=jytest

FAL_CLIENT 指定一个数据库的DB_UNIQUE_NAME,通常该库为standby角色。

例如:FAL_CLIENT=jyrac1

提示:FAL是Fetch Archived Log的缩写

DB_FILE_NAME_CONVERT 在做duplicate复制和传输表空间的时候这类参数讲过很多遍,该参数及上

述内容中同名参数功能,格式等完全相同。

LOG_FILE_NAME_CONVERT 同上
STANDBY_FILE_MANAGEMENT 如果primary数据库数据文件发生修改(如新建,重命名等)则按照本参数

的设置在standby中做相应修改。设为AUTO表示自动管理。设为MANUAL

表示需要手工管理。

例如:STANDBY_FILE_MANAGEMENT=AUTO