OCP 11gR2: Administration II 練習筆記 (完)

Lesson 10

===============================================================================

- OMF (db_create_dest)

- block space, 11gR2 PCT_FREE default 10%

- Row chaining and migration will affect performance, due to pct_free 10% db will allow update statement in

 same data block. However, if block not enough space. It will cause row migration, db will migrate half records

 to new data block. Segment advisor will discover row migration. It will update row header and cause row chaining.

 In segment(table, index, undo…) have a lot extent (group of data block), each extent have BMB (bitmap block) to

 keep check each data block HDD physical address, if data block change, BMB also need to update records by

 automatic space management. It will save dba time to shrink table by manual.

- extent allocation by uniform or auto-allocate

        Check extent map in EM > tbs > view tbs > show tbs contents

- Segment Advisor: for tablespace extent check

        EM > advisor central > segment advisor > add tbs > submit

- Allocating Space (if: deferred_segment_creation=true)

 Create table without insert data, will not use storage in tablespace

 Config by spfile or alter session or by create table clause

* unusable indexes, if statistics not yet gather the index, it will not use storage

- compression (to use table compression for DML, tbs compression must enable)

 Option: {compress [Basic | for OLTP | nocompress}

        "Basic" is for bulk import (SQL Loader /direct-path), command: "create table ….compress basic;"

        "for OLTP" support DML, command: "create table …. compress for OLTP;"

        - not support cluster table (IOT, indexes organized table )

        - will not compress existing records ( for existing records, you need to use datapump export and import)

        - predick compression level by dbms_compression package

e.g.          set serveroutput on;

                declare

                 lv_cmp_ratio number;

                 lv_comptype_str varchar2(300);

                 lv_BLKCNT_CMP number;

                 lv_BLKCNT_UNCMP number;

                 lv_ROW_CMP number;

                 lv_ROW_UNCMP number;

                begin

                 dbms_compression.GET_COMPRESSION_RATIO(

                 SCRATCHTBSNAME=>'USERS',

                 OWNNAME=>'SCOTT',

                 TABNAME=>'BIGFILE',

                 PARTNAME =>null,

                 COMPTYPE =>2,

                 BLKCNT_CMP=>lv_BLKCNT_CMP,

                 BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,

                 ROW_CMP =>lv_ROW_CMP,

                 ROW_UNCMP =>lv_ROW_UNCMP,

                 CMP_RATIO=>lv_cmp_ratio,

                 COMPTYPE_STR=>lv_COMPTYPE_STR);

                 dbms_output.put_line('==============================');

                 dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);

                 dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);

                 dbms_output.put_line('3. Compression Type :'||lv_comptype_str);

                 dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);

                 dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);

                 dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);

                 dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);

                 dbms_output.put_line('===============================');

                end;

                /

- proactive tablespace monitoring

        By thresholds, tablespace alerts or warning (MMON)

        By AWR compare snapshot between 2 times

- Segments Shrink

        To find out high water mark by command: "select block, empty_blocks from dba_tables where…;"

e.g.  alter table 'name' enable row movement;

        alter table 'name' shrink space compact;

        alter table 'name' shrink space [ cascade ];

* the cascade clause extends the segment shrink operation to all dependent (index)

                Or by segment advisor

        EM > advisor central > segment advisor > tbs > select all permanent tbs > submit

* segment advisor support permanent tbs only

* automatic maintenance will run segment advisor everyday, check the report in scheduler central >

 Automatic maintenance task) to find any recommendation

- Resumable Space Allocation (if tbs full, process will suspend)

        By spfile resumable_timeout or session

        It will resume when space problem fixed, otherwise it will abort the process when over the timeout setting.

- 4 KB-sector disks (old HDD usually are 512B sector, new 1 TB HDD is 4 KB)

 * 11gR2 auto detect sector size, if set the db_block_size to 4096 (4KB), database only need read/write 1 time

  for 1 data block to improve performance. Also BIOS need set to native mode (emulation mode will use more

  loading). In native mode the archivelog and redo log also consider to use 4KB by "blocksize 4096"

- Transporting Database

        System, sysaux tbs cannot transport

Flow:                make tbs read-only

                Use datapump to extract metadata

                Check platform endian, if endian format not same, convert datafiles by RMAN (better perform on target)

                "select * from v$transportable_platform;" and "select platform_name from v$database;"

                Copy datafiles and dump file to target

                Use datapump import metadata and make tablespace read/write

e.g.          exec dbms_tt.transport_set_check('tbs',true);

                select * from transport_set_violations;

                alter tablespace 'name' read only;

                mkdir -p /tmp/transport

                create or replace directory transport as '/tmp/transport';

                expdp \'/ as sysdba \' logfile=trans.log dumpfile=trans.dmp directory=transport transport_tablespace='name'

                copy tbs.dbf to /tmp/transport

                copy all /tmp/transport folder to target server

                in target server

                move dbf to organial path which like source server

                create or replace directory transport as '/tmp/transport';

                impdp \'/ as sysdba \' logfile=trans.log dumpfile=trans.dmp directory=transport transport_tablespace='name'

                alter tablespace 'name' read write;

                if need convert

                RMAN > CONVERT DATAFILE '/tmp/transport/name.dbf' TO PLATFORM="Solaris[tm] OE (64-bit)"

                FROM PLATFORM="Microsoft Windows x86 64-bit"

                DB_FILE_NAME_CONVERT= '/tmp/transport/', '/tmp/transport/import';

- Duplicate database



OCP 11gR2: Administration II 練習筆記 (九)