物理 dataguard add tempfile

0

Posted on by

记一下在物理dataguard上增加tempfile 的过程。。

–版本:

SQL> select * from v$version;

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

 

–关闭同步
SQL> alter database recover managed standby database cancel;

 

–现有的tempfile

SQL> select name from v$tempfile;

NAME
——————————————————————————–
+DATADG/oradbca/tempfile/temp.304.810137387

 

–增加tempfile 脚本

alter tablespace temp add tempfile '+DATADG/oradbca/tempfile/temp02.dbf' size 10g autoextend off;
alter tablespace temp add tempfile '+DATADG/oradbca/tempfile/temp03.dbf' size 10g autoextend off;
alter tablespace temp add tempfile '+DATADG/oradbca/tempfile/temp04.dbf' size 10g autoextend off;
alter tablespace temp add tempfile '+DATADG/oradbca/tempfile/temp05.dbf' size 10g autoextend off;

 

–启动同步
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

 

–后面检查一下dataguard同步。

–结局

 

 

 

 

oracle 11g securefiles

0

Posted on by

    oracle 11g推出了新一代LOB:SecureFiles ,这个全新的数据类型同时还提供了高级的下一代功能与优点,可以存储非结构化数据,智能压缩,取消重复和透明加密等功能。

    使用 SecureFiles 时,块大小介于 Oracle 数据块大小到 64 MB 之间。Oracle DB 尝试使数据集中在磁盘的相邻物理位置,从而将内部碎片降到最低。通过使用可变的块大小,SecureFiles 避免对不必要的大型 LOB 数据块进行版本化。 SecureFiles 还提供了新的客户机/服务器网络层,从而允许在支持更高读写性能的服务器和客户机之间进行高速数据传输。SecureFiles 自动确定生成重做和还原的最有效方法,因而不需要用户定义参数。SecureFiles 自动确定是仅为更改生成重做和还原,还是通过生成完整的重做记录创建新版本。 由于 SecureFiles 需要维护各种内存中统计信息以帮助有效地分配内存和空间,所以将其设计为可自适应的智能工具。这样,由于减少了很难使用不可预测的负荷进行优化的可优化参数数量,可管理性有所提高。

    使用SecureFiles的条件:

     1.COMPATIBLE初始化参数比较要设置为11.1或者更高才可以使用SecureFiles

     2.通过设置初始化参数DB_SECUREFILE来确定是否启用SecureFiles

     3.创建 securefile 的表空间应启用自动段空间管理 (ASSM)

 

DB_SECUREFILE参数的说明:

     使用 DB_SECUREFILE 初始化参数,其中有效值为:

       •ALWAYS:尝试将所有 LOB 创建为 SecureFile LOB,但是仅可将自动段空间管理 (ASSM) 表空间外的任何 LOB 创建为 BasicFile LOB

       •FORCE:强制将所有 LOB 创建为 SecureFile LOB

       •PERMITTED:允许创建 SecureFiles(默认值)

       •NEVER:禁止创建 SecureFiles

      •IGNORE:禁止创建 SecureFiles,并忽略使用 SecureFiles 选项强制创建 BasicFiles 而导致的任何错误

      如果指定了NEVER,则任何指定为SecureFiles 的LOB 均被创建为BasicFiles。如果对BasicFiles 使用任何SecureFiles 特定的存储选项和功能(如压缩、加密和取消重复),则会导致异常错误。将对任何未指定的存储选项使用BasicFiles 默认值。
    如果指定了ALWAYS,则系统中创建的所有LOB 均会被创建为SecureFiles。必须在ASSM 表空间中创建LOB,否则会发生错误。将忽略所有指定的BasicFiles 存储选项。

    可以使用ALTER SYSTEM 命令更改所有存储的SecureFiles 默认值。

       SQL>Altersystem set db_securefile=’ALWAYS’;(可以在线修改,不需要重启)

 

 

Securefiles属性如下:

  DEDUPLICATE/KEEP_DUPLICATES: DEDUPLICATE选项允许指定在一个LOB列中有两行或者更多行的时候,所有行都共享同样的数据块。相反的就是KEEP_DUPLICATES选项。11g数据库使用一个的哈希索引来检测重复性并且联合LOBs的内容到一个单一的拷贝里面,减少存储空间并且简化存储管理。默认是KEEP_DUPLICATES

  VALIDATE:对SecureFiles执行一个字节到字节的比较,确定SecureFiles都是同样的安全哈希值。

  COMPRESS/NOCOMPRESS:决定是否启用LOB压缩。还有COMPRESS HIGH,COMPRESS MEDIUM ,COMPRESS LOW的压缩。默认是NOCOMPRESS 。

  ENCRYPT/DECRYPT:决定是否启用LOB加密。如果一旦设置好了加密,就只能用ALTER TABLE REKEY命令来更新加密算法或者是加密值。默认是DECRYPT

  RETENTION:只能影响使用ALTER TABLE语句之后所创建的空间。
                     (1)  MAX 在达到段MAXSIZE 后重新使用旧版本。
                     (2)  MIN 在指定的最短时间内保留旧版本。
                     (3)  AUTO 是默认设置,主要对空间和时间进行折中使之达到平衡。这是自动确定的。
                    (4)  NONE 尽可能重用旧版本。

  对于Securefiles的访问,Oracle11g数据库是通过使用DBMS包来实现的。主要是通过DBMS_LOB包和DBMS_SPACE包来访问。

  DBMS_LOB包:LOBs继承LOB列的设定的属性,当然也可以通过LOB locator API配置成每个LOB级别上。但是LONG API不能用于配置这些LOB设定,必须使用DBMS_LOB包来设定这些属性:

  DBMS_LOB.GETOPTIONS:可以用来获取设定,每个选项类型的预定义的一个常量对应于一个整数将会返回。

  DBMS_LOB.SETOPTIONS:用于设定特性并且允许在每一个LOB基础上设定特性,会覆盖所有默认的LOB设定。

  DBMS_LOB.GET_DEDUPLICATE_REGIONS:这个存储过程用于输出在一个LOB中不重复区域的记录收集。LOB级别上只包含一个不重复区域。

  DBMS_SPACE.SPACE_USAGE:这个存储过程用于返回LOB空间使用的负载信息。返回在LOB段中所有LOB使用的磁盘空间的blocks的数量。这个存储过程目前只能用于ASSM创建的表空间,不支持属于BasicFiles那部分LOB使用的空间。

 

可以通过视图dba_lobs查看相关信息

select * from dba_lobs where owner='ORADBCA'

 

示例:

1.智能LOB压缩

--创建表 
SQL> CREATE TABLE oradbca.clob_test1 
  2  (id         NUMBER, clob_data  CLOB)
  3   LOB(clob_data) STORE AS BASICFILE;

Table created.

 SQL> CREATE TABLE oradbca.clob_test2 
  2  ( id         NUMBER,  clob_data  CLOB) 
  3  LOB(clob_data) STORE AS SECUREFILE;

Table created.

SQL> 
SQL> 
SQL> CREATE TABLE oradbca.clob_test3 
  2  (id         NUMBER, clob_data  CLOB) LOB(clob_data)
  3   STORE AS SECUREFILE (COMPRESS HIGH);

Table created.

SQL> 
SQL> 
SQL> CREATE TABLE oradbca.clob_test4
  2   (  id         NUMBER,  clob_data  CLOB) LOB(clob_data) 
  3  STORE AS SECUREFILE (COMPRESS MEDIUM);

Table created.
SQL> 
SQL> CREATE TABLE oradbca.clob_test5
  2   (  id         NUMBER,  clob_data  CLOB) LOB(clob_data) 
  3  STORE AS SECUREFILE (COMPRESS low);
Table created.
--批量插入数据
12:44:00 SQL> DECLARE
12:44:01   2    l_clob CLOB := RPAD('X', 5000, 'X');
12:44:01   3  BEGIN
12:44:01   4    FOR i IN 1 .. 1000000 LOOP
12:44:01   5      INSERT INTO oradbca.clob_test1 VALUES (i, l_clob);
12:44:01   6    END LOOP;
12:44:01   7    COMMIT;
12:44:01   8  END;
12:44:01   9  /

PL/SQL procedure successfully completed.

12:46:51 SQL> 


12:47:23 SQL> 
12:47:23 SQL> 
12:47:23 SQL> 
12:47:24 SQL> 
12:47:24 SQL> 
12:47:24 SQL> 
12:47:25 SQL> DECLARE
12:47:25   2    l_clob CLOB := RPAD('X', 5000, 'X');
12:47:25   3  BEGIN
12:47:25   4    FOR i IN 1 .. 1000000 LOOP
12:47:25   5      INSERT INTO oradbca.clob_test2 VALUES (i, l_clob);
12:47:25   6    END LOOP;
12:47:25   7    COMMIT;
12:47:25   8  END;
12:47:25   9  /

PL/SQL procedure successfully completed.

12:54:21 SQL> 



12:54:48 SQL> 
12:56:18 SQL> 
12:56:19 SQL> DECLARE
12:56:20   2    l_clob CLOB := RPAD('X', 5000, 'X');
12:56:20   3  BEGIN
12:56:20   4    FOR i IN 1 .. 1000000 LOOP
12:56:20   5      INSERT INTO oradbca.clob_test3 VALUES (i, l_clob);
12:56:20   6    END LOOP;
12:56:20   7    COMMIT;
12:56:20   8  END;
12:56:20   9  /

PL/SQL procedure successfully completed.

12:59:26 SQL> 


13:00:37 SQL> 
13:00:51 SQL> 
13:00:51 SQL> DECLARE
13:00:53   2    l_clob CLOB := RPAD('X', 5000, 'X');
13:00:53   3  BEGIN
13:00:53   4    FOR i IN 1 .. 1000000 LOOP
13:00:53   5      INSERT INTO oradbca.clob_test4 VALUES (i, l_clob);
13:00:53   6    END LOOP;
13:00:53   7    COMMIT;
13:00:53   8  END;
13:00:53   9  /

PL/SQL procedure successfully completed.

13:03:11 SQL> 

13:07:34 SQL> DECLARE l_clob CLOB := RPAD('X', 5000, 'X');
13:07:36   2  BEGIN
13:07:36   3    FOR i IN 1 .. 1000000 LOOP
13:07:36   4      INSERT INTO oradbca.clob_test5 VALUES (i, l_clob);
13:07:36   5    END LOOP;
13:07:36   6    COMMIT;
13:07:36   7  END;
13:07:36   8  /

PL/SQL procedure successfully completed.

13:09:45 SQL> 


--查询表占用空间
SELECT 'CLOB_TEST1  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST1') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST1'))
union all
SELECT 'CLOB_TEST2  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST2') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST2'))
union all
SELECT 'CLOB_TEST3  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST3') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST3'))
union all
SELECT 'CLOB_TEST4  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST4') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST4'))
union all
SELECT 'CLOB_TEST5  ' || sum(bytes / 1024 / 1024) || 'MB' mb
  FROM dba_segments
 where owner = 'ORADBCA'
   and (segment_name in ('CLOB_TEST5') or
       segment_name in (select segment_name
                           from dba_lobs
                          where owner = 'ORADBCA'
                            and table_name = 'CLOB_TEST5'));

MB
------------------------------------------------------
CLOB_TEST1  15741MB
CLOB_TEST2  16075.125MB
CLOB_TEST3  96.125MB
CLOB_TEST4  136.125MB
CLOB_TEST5  120.125MB

 

查询结果的压缩率很明显,没开压缩与普通的大字段在空间上没啥区别,

是开了压缩后,他的压缩率特别的明显。

 

 

2.取消重复

--创建带DEDUPLICATE参数的表
SQL> CREATE TABLE oradbca.clob_test6
  2  ( id         NUMBER,  clob_data  CLOB) 
  3  LOB(clob_data) STORE AS SECUREFILE  (DEDUPLICATE);

Table created.
	
--插入数据
13:34:55 SQL> DECLARE l_clob CLOB := RPAD('X', 5000, 'X');
13:34:56   2  BEGIN
  FOR i IN 1 .. 1000000 LOOP
13:34:56   3  13:34:56   4      INSERT INTO oradbca.clob_test6 VALUES (i, l_clob);
13:34:56   5    END LOOP;
13:34:56   6    COMMIT;
13:34:56   7  END;
13:34:56   8  /

PL/SQL procedure successfully completed.

13:39:23 SQL> 


--查看一下占用大小
MB
------------------------------------------------------
CLOB_TEST2  16075.125MB
CLOB_TEST6  89.125MB

可以看到在blob字段中如果出现相同的内容时,在空间占用上有很大的优化。

 

管理:

  从BasicFiles移植SecureFiles

      有两种推荐的方法来移植。这两种方法分别是交换分区和在线重定义。

  1.交换分区:

  需要和表中最大的分区相等的额外空间

  在交换的时候可以维护索引

  能够在几个小的维护窗口延展工作负载

  执行交换分区的时候需要将表或者分区离线

 

  2.在线重定义:

  不要求表或者分区离线

  可以被并行的执行

  要求额外的等于整个表或者所有LOB段大小的可用空间

  要求必须重建所有的全局索引

 

  3.如果你想升级你的BasicFiles到SecureFiles,你可以使用传统的方法来升级数据,例如CTAS/ITAS、export/import、column-to-column拷贝等等。大部分这些解决方案都要求使用两倍的磁盘空间来移植。然而,使用分区解决方案来移植可以按照每个分区来移植,从而有助于减少磁盘空间的需求。

 

 

附:官方语法:

create:

CREATE [ GLOBAL TEMPORARY ] TABLE
   [ schema.]table OF
   [ schema.]object_type
   [ ( relational_properties ) ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ OID_clause ]
   [ OID_index_clause ]
   [ physical_properties ]
   [ table_properties ] ;
 
<relational_properties> ::= 
{ column_definition
| { out_of_line_constraint
  | out_of_line_ref_constraint
  | supplemental_logging_props
  }
}
  [, { column_definition
     | { out_of_line_constraint
       | out_of_line_ref_constraint
       | supplemental_logging_props
       }
  ]...
 
<column_definition> ::= 
column data_type [ SORT ]
      [ DEFAULT expr ]
      [ ENCRYPT encryption_spec ]
      [ ( inline_constraint [ inline_constraint ] ... )
      | inline_ref_constraint 
      ]
 
<data_type> ::=
{ Oracle_built_in_datatypes
| ANSI_supported_datatypes
| user_defined_types
| Oracle_supplied_types
}
 
<Oracle_built_in_datatypes> ::=
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
 
<large_object_datatypes> ::= 
{ BLOB | CLOB | NCLOB| BFILE }
 
 <table_properties> ::=
  [ column_properties ]
  [ table_partitioning_clauses ]
  [ CACHE | NOCACHE ]
  [ parallel_clause ]
  [ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
  [ enable_disable_clause ]
  [ enable_disable_clause ]...
  [ row_movement_clause ]
  [ AS subquery ]
 
<column_properties> ::=
  { object_type_col_properties
  | nested_table_col_properties
  | { varray_col_properties | LOB_storage_clause }
    [ (LOB_partition_storage
        [, LOB_partition_storage ]...
      )
    ]
  | XMLType_column_properties
  }
  [ { object_type_col_properties
    | nested_table_col_properties
    | { varray_col_properties | LOB_storage_clause }
      [ ( LOB_partition_storage
          [, LOB_partition_storage ]...
        )
      ]
    | XMLType_column_properties
    }
  ]...
 
<LOB_partition_storage> ::=
  PARTITION partition
  { LOB_storage_clause | varray_col_properties }
    [ LOB_storage_clause | varray_col_properties ]...
  [ ( SUBPARTITION subpartition
     { LOB_storage_clause | varray_col_properties }
       [ LOB_storage_clause
       | varray_col_properties
       ]...
    )
  ]
 
<LOB_storage_clause> ::=
  LOB
  { (LOB_item [, LOB_item ]...)
      STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters)
  | (LOB_item)
      STORE AS [ SECUREFILE | BASICFILE ]
        { LOB_segname (LOB_storage_parameters)
        | LOB_segname
        | (LOB_storage_parameters)
        }
  }
 
<LOB_storage_parameters> ::=
  { TABLESPACE tablespace
  | { LOB_parameters [ storage_clause ]
    }
  | storage_clause
  }
    [ TABLESPACE tablespace
    | { LOB_parameters [ storage_clause ]
      }
    ]...
 
<LOB_parameters> ::=
  [ { ENABLE | DISABLE } STORAGE IN ROW
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  ]
 
<logging_clause> ::=
  { LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
 
<storage_clause> ::=
  STORAGE
  ({ INITIAL integer [ K | M ]
   | NEXT integer [ K | M ]
   | MINEXTENTS integer
   | MAXEXTENTS { integer | UNLIMITED }
   | PCTINCREASE integer
   | FREELISTS integer
   | FREELIST GROUPS integer
   | OPTIMAL [ integer [ K | M ]
             | NULL
             ]
   | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
   }
     [ INITIAL integer [ K | M ]
     | NEXT integer [ K | M ]
     | MINEXTENTS integer
     | MAXEXTENTS { integer | UNLIMITED }
     | MAXSIZE { { integer { K | M | G | T | P } } | UNLIMITED }
     | PCTINCREASE integer
     | FREELISTS integer
     | FREELIST GROUPS integer
     | OPTIMAL [ integer [ K | M ]
               | NULL
               ]
     | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
     ]...
  )
 
<LOB_deduplicate_clause> ::=
  { DEDUPLICATE 
  | KEEP_DUPLICATES
  }
 
<LOB_compression_clause> ::=
  { COMPRESS [ HIGH | MEDIUM ]
  | NOCOMPRESS }
 
<LOB_encryption_clause> ::=
  { ENCRYPT [ USING 'encrypt_algorithm' ] 
    [ IDENTIFIED BY password ]
  | DECRYPT 
  }
 
<XMLType_column_properties> ::= 
XMLTYPE [ COLUMN ] column
   [ XMLType_storage ]
   [ XMLSchema_spec ]
 
<XMLType_storage> ::=
STORE AS
   { OBJECT RELATIONAL
   | [ SECUREFILE | BASICFILE ] { CLOB | BINARY XML }
       [ { LOB_segname [ (LOB_parameters) ]
         | LOB_parameters
         }
         ]
 
<varray_col_properties> ::=
VARRAY varray_item 
   { [ substitutable_column_clause ]
     STORE AS [ SECUREFILE | BASICFILE ] LOB
        { [ LOB_segname ] (LOB_parameters)
        | LOB_segname 
        }
   | substitutable_column_clause
   }

 

alter:

ALTER TABLE [ schema.]table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table_clauses
  | move_table_clause
  ]
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    ]...
  ] ;
 
 
<column_clauses> ::=
  { { add_column_clause
    | modify_column_clause
    | drop_column_clause
    }
    [ add_column_clause
    | modify_column_clause
    | drop_column_clause
    ]...
  | rename_column_clause
  | modify_collection_retrieval
    [ modify_collection_retrieval ]...
  | modify_LOB_storage_clause
    [ modify_LOB_storage_clause ] ...
  | alter_varray_col_properties
    [ alter_varray_col_properties ]
  }
 
<modify_LOB_storage_clause> ::=
MODIFY LOB (LOB_item) (LOB_deduplicate_clause [LOB_deduplicate_clause ] )
 
<LOB_deduplicate_clause> ::=
  { storage_clause
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | REBUILD FREEPOOLS
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  | allocate_extent_clause
  | shrink_clause
  | deallocate_unused_clause
  }
    [ storage_clause
    | PCTVERSION integer
    | RETENTION
    | FREEPOOLS integer
    | REBUILD FREEPOOLS
    | modify_LOB_sharing_clause
    | LOB_compression_clause
    | LOB_encryption_clause
  | { CACHE | { NOCACHE | CACHE READS } [ logging_clause ] }
    | shrink_clause
    | deallocate_unused_clause
    ]...

oracle 11g interval partitioning

0

Posted on by

 11g在分区上有了很大的改进,其中有一个特性是Interval-Partition,他是range分区的派生,自动创建指定间隔的分区。

  在 11g 之前,如果数据中出现未能匹配分区条件的情况,系统会拒绝进行数据操作。

对时间类型和数字类型的支持:

1.对于采用date类型或TIMESTAMP类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区,分别通过如下函数转换

numtoyminterval ( n, { 'YEAR'|'MONTH'})
numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})

2. Interval (number) 即多少值一个分区

 

创建示例

1.时间类型

--创建表
SQL> CREATE TABLE oradbca.intervalpart1 (c1 NUMBER, c2 DATE)
  2  PARTITION BY RANGE (c2)
  3     INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
  4     (PARTITION part1
  5         VALUES LESS THAN (TO_DATE ('02/01/2013', 'MM/DD/YYYY'))
  6    );

Table created.

--查询只有一个分区
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART1';

TABLE_NAME       PARTITION_NAME           HIGH_VALUE
---------------- -----------------         -------------------------------------------------------
INTERVALPART1       PART1                 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--增加2行记录一行是2月份的,一行是1月份的
SQL>   insert into oradbca.intervalpart1 values (1,TO_DATE ('02/02/2013', 'MM/DD/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> insert into oradbca.intervalpart1 values (1,TO_DATE ('01/01/2013', 'MM/DD/YYYY')); 

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL>   Select Count(1) From oradbca.intervalpart1;

  COUNT(1)
----------
         2

--增加了2月份的分区
SQL> 
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART1';

TABLE_NAME      PARTITION_NAME    HIGH_VALUE
-------------- ----------------- -------------------------------------------------------------
INTERVALPART1        PART1        TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVALPART1        SYS_P105     TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--插入当前时间的分区
SQL>  insert into oradbca.intervalpart1 values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

--又有一个分区新建了,如果插入的时间在两个月以后或者更久,则Oracle只生成必须的分区,并不会生成连续分区。
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART1';

TABLE_NAME         PARTITION_NAME    HIGH_VALUE
----------------- ---------------    --------------------------------------------------------------------------------
INTERVALPART1      PART1             TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVALPART1      SYS_P105          TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVALPART1      SYS_P106          TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> 

 

2.数字类型

----创建表
SQL> CREATE TABLE oradbca.intervalpart2 (c1 NUMBER, c2 DATE)
  2   PARTITION BY RANGE (c1)
  3     INTERVAL (100)
  4      (PARTITION part1
  5          VALUES LESS THAN (100)
  6    );

Table created.

--查询只有一个分区
SQL> select table_name, partition_name, HIGH_VALUE
  2     from dba_tab_partitions t
  3    where table_name = 'INTERVALPART2';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ -------------------------
INTERVALPART2                  PART1                          100

SQL> 
--增加2行记录一行是1,一行是101
SQL> insert into  oradbca.intervalpart2 values (1,sysdate);

1 row created.

SQL> insert into  oradbca.intervalpart2 values (101,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL>  Select Count(1) From  oradbca.intervalpart2;

  COUNT(1)
----------
         2

--增加了101的分区
SQL> select table_name, partition_name, HIGH_VALUE
  2  from dba_tab_partitions t
  3  where table_name = 'INTERVALPART2';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------
INTERVALPART2                  PART1                          100
INTERVALPART2                  SYS_P107                       200

--插入1101数值的分区
SQL>  insert into  oradbca.intervalpart2 values (1101,sysdate);

1 row created.

SQL> commit;

Commit complete.

--又有一个分区新建了,如果插入的数值是差别很大或者更久,则Oracle只生成必须的分区,并不会生成连续分区。
SQL> select table_name, partition_name, HIGH_VALUE
  2  from dba_tab_partitions t
  3  where table_name = 'INTERVALPART2';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------
INTERVALPART2                  PART1                          100
INTERVALPART2                  SYS_P107                       200
INTERVALPART2                  SYS_P108                       1200

 

管理:

1.自动分区与自动分区的转换

--创建普通分区表
SQL> CREATE TABLE oradbca.intervalpart3 (c1 NUMBER, c2 DATE)
  2         PARTITION BY RANGE (c1)
  3         (PARTITION part1 VALUES LESS THAN (100)
  4  );

Table created.

SQL>  insert into  oradbca.intervalpart3 values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.
--当插入超出值时会报错
SQL>  insert into  oradbca.intervalpart3 values (101,sysdate);
 insert into  oradbca.intervalpart3 values (101,sysdate)
                   *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

--设置100数值为分区
SQL> alter table  oradbca.intervalpart3 set INTERVAL(100);

Table altered.

--插入成功
SQL> insert into  oradbca.intervalpart3 values (101,sysdate);

1 row created.

SQL> commit;

Commit complete.

--新生成分区
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART3';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------------------
INTERVALPART3                  PART1                          100
INTERVALPART3                  SYS_P109                       200

SQL> 
SQL> 
--也可以设置个更高的值
SQL> alter table  oradbca.intervalpart3 set INTERVAL(300);

Table altered.
--插入数据
SQL> insert into  oradbca.intervalpart3 values (201,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into  oradbca.intervalpart3 values (401,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into  oradbca.intervalpart3 values (501,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into  oradbca.intervalpart3 values (601,sysdate);

1 row created.

SQL> commit;

Commit complete.

--更改的值生效,之前的分区不影响。
SQL> select table_name, partition_name, HIGH_VALUE
  2    from dba_tab_partitions t
  3   where table_name = 'INTERVALPART3';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------
INTERVALPART3                  PART1                          100
INTERVALPART3                  SYS_P109                       200
INTERVALPART3                  SYS_P110                       500
INTERVALPART3                  SYS_P111                       800

SQL> 
--关闭自动分区
SQL> alter table  oradbca.intervalpart3 set INTERVAL();

Table altered.

--插入数据不成功
SQL>  insert into  oradbca.intervalpart3 values (801,sysdate);
 insert into  oradbca.intervalpart3 values (801,sysdate)
                   *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

2.配置多个表空间的分区存储

alter table oradbca.intervalpart3 set STORE IN (tablespace1, tablespace2, tablespace3);

这3个表空间,分区会循环分配到这3个表空间。

3. rename 分区名

分区:
alter table ORADBCA.INTERVALPART3  rename partition SYS_P109 to part2;

子分区:
alter table ORADBCA.INTERVALPART3  rename partition SYS_P109 to part2;

不设置sec_case_sensitive_logon参数让密码大小写不敏感的实验

1

Posted on by

都知道在11g中密码大小写敏感了,同时可以设置sec_case_sensitive_logon参数可以忽略大小写敏感。

方法目前我想到的有2种,一种是可行的,还有一种是下面的实验主要内容。

方法1:就是在10g里新建用户名通过expdp,然后impdp倒入到11g的数据库中;

方法2:通过修改表 SYS.USER$ 来实现;

下面是实验内容:

SQL> select * from v$version;

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

SQL> 
SQL> show parameter logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 
SQL> 
创建用户名:
SQL> create user oradbca identified by oracle;

User created.

SQL> grant create session to oradbca;

Grant succeeded.
小写密码登入成功
SQL> conn oradbca/oracle
Connected.
大写密码登入不成功
SQL> conn oradbca/ORACLE
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> 
SQL> 
SQL> conn / as sysdba
Connected.
SQL>  select t.username,t.account_status,t.password_versions from dba_users t where t.username='ORADBCA';

USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- --------
ORADBCA                        OPEN                             10G 11G


SQL> select name,password,spare4 from SYS.USER$ t where name='ORADBCA';


NAME       PASSWORD                       SPARE4
---------- ------------------------------ ------------------------------------------------------------------
ORADBCA    CC5873303EECAD49               S:89B029335C4B8CDB5E72C2D3232F5CBFF94579A7083758F667EE9473B2D5

由于password_versions的优先级大于sec_case_sensitive_logon这个参数,

所以我想能不能修改这个password_versions值来让大小写不敏感。

清空spare4值来让password_versions显示为10g,实验步骤为:

SQL> update SYS.USER$ t set SPARE4='' where name='ORADBCA';

1 row updated.

SQL> commit;

Commit complete.

SQL> 
SQL> select t.username,t.account_status,t.password_versions from dba_users t where t.username='ORADBCA';

USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- --------
ORADBCA                        OPEN                             10G

SQL> select name,password,spare4 from SYS.USER$ t where name='ORADBCA';

NAME       PASSWORD                       SPARE4
---------- ------------------------------ ------------------------------------------------
ORADBCA    CC5873303EECAD49

已经看到password_versions为10g了,现在我们验证一下是否大小写不敏感。

SQL> 
SQL> conn oradbca/oracle
Connected.
SQL> conn oradbca/ORACLE
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

看到还是不行,通过朋友的咨询,修改内部数据字典需要flush shared_pool

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL>  alter system flush shared_pool;

System altered.

SQL> conn oradbca/ORACLE
Connected.
	

现在看到是可以了,密码忽略了大小写。

 

 

 

Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files

0

Posted on by

由于某些原因,需要把物理dataguard上的控制文件重建。

主要步骤为:

Step 1: Create the Standby control file on primary database 

Step 2: Copy the controlfile backup to the standby system 

Step 3: Shutdown, restore, rename.

 

具体步骤为:

Step 1 : Create the Standby control file on primary database. 

$ export ORACLE_SID=DEL 
$rman target / 
RMAN> backup current controlfile for standby format 'stdbyctl.bkp'; 
RMAN> EXIT;

stdbyctl.bkp file will be created in "$ORACLE_HOME/dbs" (Unix) or "$ORACLE_HOME/database" (Windows). 

Step 2. Copy the controlfile backup to the standby system 

Using ftp/scp move stdbyctl.bkp to standby system 

Step 3: Shutdown, restore, rename.

A. Shutdown all instances of the standby. 

$ export ORACLE_SID=MUM 
$sqlplus / as sysdba 
SQL> shutdown immediate 
ORA-01109: database not open 

Database dismounted. 
ORACLE instance shut down. 

B. Depending on the location of the logfiles on the standby server remove all online and standby redo logs from the standby directories Using an Operating System utility or ASMCMD and make sure that you have the LOG_FILE_NAME_CONVERT parameter defined to translate any directory paths. 

C. Startup one instance of Standby database in nomount stage: 

$sqlplus / as sysdba 
SQL> startup nomount 
ORACLE instance started. 

Total System Global Area 209715200 bytes 
Fixed Size 1248116 bytes 
Variable Size 75498636 bytes 
Database Buffers 125829120 bytes 
Redo Buffers 7139328 bytes 

D. Connect to RMAN with nocatalog option and Restore the standby control file: 

$rman nocatalog target / 
RMAN> restore standby controlfile from '\tmp\stdbyctl.bkp'; 

Starting restore at 29-AUG-08 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=155 devtype=DISK 

channel ORA_DISK_1: restoring control file 
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17 
output filename=+DATA1/del/controlfile/current.257.661096899 
Finished restore at 29-AUG-08 

E. Mount standby database 

RMAN> alter database mount; 

database mounted 

F. Catalog the datafiles of standby database 

Below command will give you a list of files and ask if they should all be catalog. Review the list and say YES if all the datafiles are properly listed 
In below command while cataloging the files, the string specified should refer to the diskgroup/filesystem destination of the standby data files. 

RMAN> catalog start with '+DATA1/MUM/DATAFILE/'; 

Starting implicit crosscheck backup at 29-AUG-08 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=155 devtype=DISK 
Crosschecked 10 objects 
Finished implicit crosscheck backup at 29-AUG-08 

Starting implicit crosscheck copy at 29-AUG-08 
using channel ORA_DISK_1 
Finished implicit crosscheck copy at 29-AUG-08 

searching for all files in the recovery area 
cataloging files… 
cataloging done 

List of Cataloged Files 
======================= 
File Name: +fra/MUM/BACKUPSET/2008_07_28/nnndf0_TAG20080728T113319_0.296.661260801 
File Name: +fra/MUM/BACKUPSET/2008_07_28/ncsnf0_TAG20080728T113319_0.297.661260847 
File Name: +fra/MUM/CONTROLFILE/backup.272.661096103 

searching for all files that match the pattern +DATA1/MUM/DATAFILE/ 

List of Files Unknown to the Database 
===================================== 
File Name: +data1/MUM/DATAFILE/SYSTEM.258.661097855 
File Name: +data1/MUM/DATAFILE/SYSAUX.259.661097855 
File Name: +data1/MUM/DATAFILE/UNDOTBS1.260.661097855 
File Name: +data1/MUM/DATAFILE/USERS.261.661097855 

Do you really want to catalog the above files (enter YES or NO)? YES 
cataloging files… 
cataloging done 

List of Cataloged Files 
======================= 
File Name: +data1/MUM/DATAFILE/SYSTEM.258.661097855 
File Name: +data1/MUM/DATAFILE/SYSAUX.259.661097855 
File Name: +data1/MUM/DATAFILE/UNDOTBS1.260.661097855 
File Name: +data1/MUM/DATAFILE/USERS.261.661097855

NOTE: 
a) This will only work if you are using OMF. If you are using ASM without OMF you have to catalog all non-OMF Datafiles as Datafile Copies manually using

RMAN> catalog datafilecopy '<File-Specification>';

b) If you have Datafiles on different Diskgroups you have to catalog from all Diskgroups, of course.

G. Commit the changes to the controlfile 

RMAN> switch database to copy; 

datafile 1 switched to datafile copy "+DATA1/mum/datafile/system.258.661097855" 
datafile 2 switched to datafile copy "+DATA1/mum/datafile/undotbs1.260.661097855" 
datafile 3 switched to datafile copy "+DATA1/mum/datafile/sysaux.259.661097855" 
datafile 4 switched to datafile copy "+DATA1/mum/datafile/users.261.661097855" 

RMAN> EXIT; 

H. Re-enable flashback on the standby database. 
$sqlplus / as sysdba 
SQL> alter database flashback off; 

Database altered. 

SQL> alter database flashback on; 

Database altered. 

I. Query v$log and clear all online redo log groups 

SQL> select group# from v$log; 

GROUP# 
———- 


SQL> alter database clear logfile group 1; 

Database altered. 

SQL> alter database clear logfile group 2; 

Database altered. 

SQL> alter database clear logfile group 3; 

Database altered. 

J. Query v$standby_log and clear all standby redo logs

SQL> select group# from v$standby_log; 

GROUP# 
———- 


SQL> alter database clear logfile group 4; 

Database altered. 

SQL> alter database clear logfile group 5; 

Database altered. 

SQL> alter database clear logfile group 6; 

Database altered. 

Recreate the standby redo logs on standby database if standby redo logs are not present on the primary.

SQL> select group# from v$standby_log; 

no row selected 

SQL> alter database add standby logfile group 4 size 50m; 

Database altered. 

SQL> alter database add standby logfile group 5 size 50m; 

Database altered. 

SQL> alter database add standby logfile group 6 size 50m; 

Database altered. 

K. Start Managed recovery process on standby 

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

Database altered. 

SQL> exit