GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

INDEX SKIP SCAN

INDEX SKIP SCAN

概要

INDEX SKIP SCANの概要について記載します。

INDEX SKIP SCAN

INDEX SKIP SCAN(9i~)により第1キーが抽出条件に無くてもインデックスを使用する機能がありますが、この実行計画が選択される条件として 第1キーのカーディナリティが非常に低い値である必要があります。
そうでない場合は、FULL SCANのほうが低コストと判断されます。

複合索引の付与列の順序の効率が良くないケース

  • 下記のような表に対して、①DEPTNO+②EMPNOの順で索引を作成している f:id:undercovergeek:20210227105454p:plain

複合索引の2列目だけの条件でクエリ実行

f:id:undercovergeek:20210227105521p:plain

複合索引をカーディナリティの高い列から付与することで改善

  • 複合索引を(①EMPNO + ②DEPTNOで再作成)
CREATE INDEX idx_comp
ON  emp2(
        empno,
        deptno
    )
;

f:id:undercovergeek:20210227105721p:plain

フラッシュバックOFF表領域の再作成

概要

フラッシュバックログが見積もりより大きい場合、フラッシュバックに時間を要することがあります。
事前にDatapumpでデータをExportしている場合、表領域再作成⇒IMPDPの方が早い場合があるため、再作成の手順を整理しておきます。

現在の状況確認(DBはフラッシュバックON)

SQL> conn / as sysdba
接続されました。
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

SQL> alter session set container = pdb1;

セッションが変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

6行が選択されました。

検証用の表領域作成とフラッシュバックOFF

SQL> create tablespace fb_test
datafile '+DATA'
size 20M
autoextend on
next 5M maxsize unlimited;
  2    3    4    5  

表領域が作成されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3
         6 FB_TEST              YES       NO        YES                          3 <---★

7行が選択されました。

SQL> alter tablespace fb_test flashback off;

表領域が変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3
         6 FB_TEST              YES       NO        NO                           3 <---★

7行が選択されました。

表領域とデータファイル削除

SQL> drop tablespace fb_test including contents and datafiles cascade constraints;

表領域が削除されました。

                          ▽アラートログ
                          2021-02-03T17:08:37.868433+09:00
                          PDB1(3):drop tablespace fb_test including contents and datafiles cascade constraints
                          2021-02-03T17:08:40.986229+09:00
                          PDB1(3):Deleted Oracle managed file +DATA/ORCL/B97C79C922B359DFE0536638A8C072ED/DATAFILE/fb_test.289.1063559079
                          PDB1(3):Completed: drop tablespace fb_test including contents and datafiles cascade constraints

SQL> create tablespace fb_test
datafile '+DATA'
size 20M
autoextend on
next 5M maxsize unlimited;
  2    3    4    5  
表領域が作成されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3
         6 FB_TEST              YES       NO        YES                          3 <---★

7行が選択されました。

結果

特に問題なく、同名表領域の作成とフラッシュバックONの設定が可能でした。
フラッシュバックに時間を要し、且つ事前に特定断面のダンプファイルがある場合は、フラッシュバックするのではなく、
表領域再作成後にIMPORTの方が早い可能性があります。

表領域レベルの圧縮

概要

表領域レベルの圧縮の動作を確認していきます。

マニュアル

Oracle® Database 管理者ガイド 19c

  13.2.4 デフォルト圧縮属性を持つ表領域

  表領域の作成時に、表領域に作成されるすべての表と索引またはそれらのパーティションをデフォルトで圧縮するように指定できます。
    - デフォルト圧縮属性を持つ表領域について
      表領域を作成する際に、表領域に作成されるすべての表および索引のデータのデフォルト圧縮を指定できます。
      デフォルト圧縮レベルは、表領域を構成するパーティションにも適用されます。このデータを圧縮するとディスク使用量を削減できます。
    - デフォルト圧縮属性を使用した表領域の作成
      表領域を作成するときに表圧縮のタイプを指定するには、DEFAULTキーワードを使用し、その後に圧縮タイプを含む表圧縮句を指定します。
      DEFAULTキーワードを使用して、その後に索引圧縮句と索引圧縮タイプを指定すると、索引圧縮のタイプを指定することもできます。

実行例

  • 圧縮表領域作成(COMP_TEST)
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> create tablespace comp_test datafile '+DATA' DEFAULT ROW STORE COMPRESS ADVANCED;

表領域が作成されました。

SQL> select f.file_id,f.tablespace_name,f.file_name,f.bytes,f.blocks,f.autoextensible,t.compress_for
from dba_tablespaces t,dba_data_files f
where t.tablespace_name = f.tablespace_name 
order by f.file_id;
  2    3    4  
   FILE_ID TABLESPACE_NAME      FILE_NAME                                                                                       BYTES     BLOCKS AUTOEXTEN COMPRESS_FOR
---------- -------------------- ------------------------------------------------------------------------------------------ ---------- ---------- --------- --------------------
         9 SYSTEM               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/system.284.1054420929                  241172480      29440 YES
        10 SYSAUX               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/sysaux.282.1054420693                 1672478720     204160 YES
        11 UNDOTBS1             +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/undotbs1.283.1054420713                209715200      25600 YES
        12 USERS                +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/users.286.1054421063                    38010880       4640 YES
        13 UNDO_2               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/undo_2.304.1054459615                  209715200      25600 YES
        14 EXAMPLES             +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/examples.329.1056635519               1073741824     131072 YES
        16 COMP_TEST            +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/comp_test.413.1056984971               104857600      12800 YES       ADVANCED
  • 比較用非圧縮表作成(EMP2)
SQL> create table emp2 as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp2 modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert into emp2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  
PL/SQLプロシージャが正常に完了しました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
;
  2    3    4    5    6  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
DEPT                 USERS                DISABLED
EMP                  USERS                DISABLED
BONUS                USERS                DISABLED
SALGRADE             USERS                DISABLED
DUMMY                USERS                DISABLED
EMP2                 USERS                DISABLED

SQL> SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
  2    3    4    5    6    7  
SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
-------------------- -------------------- -------------------- ----------
EMP                  TABLE                USERS                        64
EMP2                 TABLE                USERS                     33792
  • 圧縮表領域に表作成(EMP_COMP)
SQL> create table emp_comp tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

SQL> alter table emp_comp modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert into emp_comp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  

PL/SQLプロシージャが正常に完了しました。

SQL> SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552

SQL> select SEGMENT_NAME, MB, MB/decode(NOCOMP_SIZE, 0, MB, NOCOMP_SIZE)*100 as RATIO
  from (select SEGMENT_NAME, MB, (lag(MB, 1, 0) over (order by MB DESC)) NOCOMP_SIZE
          from (select SEGMENT_NAME, sum(BYTES)/1024/1024 MB
                  from USER_SEGMENTS
                 where SEGMENT_NAME like 'EMP%'
                 group by SEGMENT_NAME
               )
       );
  2    3    4    5    6    7    8  
SEGMENT_NAME                                     MB      RATIO
---------------------------------------- ---------- ----------
EMP2                                             33        100
EMP_COMP                                         23 69.6969697
EMP                                           .0625  .27173913
  • ダイレクトパスインサート(EMP_DIRECT)
SQL> create table emp_direct tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp_direct modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert /*+ APPEND */ into emp_direct (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

SQL> SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
  2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
  • IMPDP(⇒EMP_IMPDP)
SQL> create table emp_impdp tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp_impdp modify (empno number(10));

表が変更されました。

SQL> alter session set container = pdb1;

セッションが変更されました。

SQL> create or replace directory SCOTT_DIR as '/u01/app/oracle/admin/orcl/dpdump';

ディレクトリが作成されました。

SQL> grant read, write on directory SCOTT_DIR to SCOTT;

権限付与が成功しました。

SQL> grant exp_full_database to SCOTT;

権限付与が成功しました。

SQL> grant imp_full_database to SCOTT;

権限付与が成功しました。
$ expdp system/system@pdb1 directory=scott_dir tables=scott.emp2 logfile=emp2.log

Export: Release 19.0.0.0.0 - Production on 金 1120 17:14:48 2020
Version 19.8.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
"SYSTEM"."SYS_EXPORT_TABLE_01"を起動しています: system/********@pdb1 directory=scott_dir tables=scott.emp2 logfile=emp2.log 
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
. . "SCOTT"."EMP2"                              26.94 MB 1009999行がエクスポートされました
マスター表"SYSTEM"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
  /u01/app/oracle/admin/orcl/dpdump/expdat.dmp
ジョブ"SYSTEM"."SYS_EXPORT_TABLE_01"が金 1120 17:15:43 2020 elapsed 0 00:00:45で正常に完了しました

$ impdp scott/tiger@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test

Import: Release 19.0.0.0.0 - Production on 金 1120 17:35:39 2020
Version 19.8.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_FULL_01"を起動しています: scott/********@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."EMP_IMPDP"                         26.94 MB 1009999行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_FULL_01"が金 1120 17:36:22 2020 elapsed 0 00:00:40で正常に完了しました
SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_IMPDP            COMP_TEST            DISABLED
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

5行が選択されました。

SQL_ID: 7w5qn4fjgpbq8
経過: 00:00:00.01
SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
EMP_IMPDP                                TABLE                COMP_TEST                 33792

SQL> drop table emp_impdp purge;

表が削除されました。
  • IMPDP(EMP_IMPDP compression_clause:\"row store compress advanced\"付与)
$ impdp scott/tiger@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test transform=table_compression_clause:\"row store compress advanced\"

Import: Release 19.0.0.0.0 - Production on 金 1120 17:51:23 2020
Version 19.8.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_FULL_01"を起動しています: scott/********@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test transform=table_compression_clause:"row store compress advanced" 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."EMP_IMPDP"                         26.94 MB 1009999行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_FULL_01"が金 1120 17:52:04 2020 elapsed 0 00:00:36で正常に完了しました
SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP_IMPDP            COMP_TEST            ENABLED                  ADVANCED
EMP                  USERS                DISABLED

5行が選択されました。

SQL_ID: 7w5qn4fjgpbq8
経過: 00:00:00.01
SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
EMP_IMPDP                                TABLE                COMP_TEST                 23552

表領域のフラッシュバックデータベースのON/OFF

概要

表領域のフラッシュバックデータベースのONとOFFについて検証してみました

マニュアル

Oracle® Database バックアップおよびリカバリ・ユーザーズ・ガイド 19c 7.5.1 フラッシュバック・データベースの有効化

ALTER DATABASEコマンドを使用して、フラッシュバック・データベースを有効にします

フラッシュバック・ロギングを有効にする手順

    1.「高速リカバリ領域の有効化」に説明されているとおりに、リカバリ領域を構成します。
    2. データベース・インスタンスがオープンまたはマウントされていることを確認します。
       インスタンスがマウントされている場合、フィジカル・スタンバイ・データベースでないかぎり、データベースが正しく停止されている必要があります。
       他のOracle Real Application Clusters (Oracle RAC)インスタンスはどのモードでもかまいません。
    3. オプションで、DB_FLASHBACK_RETENTION_TARGETに、フラッシュバックの期間の長さを分単位で設定します。

       ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 days

    デフォルトでは、DB_FLASHBACK_RETENTION_TARGETは1日(1440分)に設定されます。

  注意:
  この設定は、データベースの起動および停止を通じて永続的にする必要があります。 

    4. データベース全体でフラッシュバック・データベース機能を有効にします。

       ALTER DATABASE FLASHBACK ON;

    5. 必要に応じて、特定の表領域のフラッシュバック・ロギングを無効にします。

    デフォルトでは、すべての永続表領域に対してフラッシュバック・ログが生成されます。
    特定の表領域のフラッシュバック・ロギングを無効にして、オーバーヘッドを軽減できます。次に例を示します。

       ALTER TABLESPACE tbs_3 FLASHBACK OFF;

    表領域のフラッシュバック・ロギングは、次のコマンドを使用して、後で再度有効にできます。

       ALTER TABLESPACE tbs_3 FLASHBACK ON;

    表領域に対してフラッシュバック・データベースを無効にする場合は、FLASHBACK DATABASEを実行する前に、データファイルをオフラインにする必要があります。 

動作確認

CDB

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

SQL> ALTER TABLESPACE USERS FLASHBACK OFF;

表領域が変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          1
         0 SYSTEM               YES       NO        YES                          2
         1 SYSAUX               YES       NO        YES                          1
         1 SYSAUX               YES       NO        YES                          2
         2 UNDOTBS1             YES       NO        YES                          1
         2 UNDOTBS1             YES       NO        YES                          2
         3 TEMP                 NO        NO        YES                          1
         3 TEMP                 NO        NO        YES                          2
         4 UNDOTBS2             YES       NO        YES                          1
         5 USERS                YES       NO        NO                           1 <---★
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

16行が選択されました。

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
orcl1                                            OPEN

SQL> ALTER TABLESPACE USERS FLASHBACK ON;
ALTER TABLESPACE USERS FLASHBACK ON
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください

[grid@rac1 ~]$ srvctl stop database -db orcl
[grid@rac1 ~]$ srvctl start database -db orcl -o mount

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
orcl1                                            MOUNTED

SQL> ALTER TABLESPACE USERS FLASHBACK ON;

表領域が変更されました。

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          1
         0 SYSTEM               YES       NO        YES                          2
         1 SYSAUX               YES       NO        YES                          1
         1 SYSAUX               YES       NO        YES                          2
         2 UNDOTBS1             YES       NO        YES                          1
         2 UNDOTBS1             YES       NO        YES                          2
         3 TEMP                 NO        NO        YES                          1
         3 TEMP                 NO        NO        YES                          2
         4 UNDOTBS2             YES       NO        YES                          1
         5 USERS                YES       NO        YES                          1 <---★
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

PDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
PDB

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

SQL> ALTER TABLESPACE USERS FLASHBACK OFF;

表領域が変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        NO                           3 <---★

6行が選択されました。

SQL> ALTER TABLESPACE USERS FLASHBACK ON;
ALTER TABLESPACE USERS FLASHBACK ON
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください

SQL> shu immediate 
プラガブル・データベースがクローズされました。
SQL> conn /as sysdba
接続されました。
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED

SQL> alter session set container = pdb;

セッションが変更されました。

SQL> ALTER TABLESPACE USERS FLASHBACK ON;
ALTER TABLESPACE USERS FLASHBACK ON
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください

[grid@rac1 ~]$ srvctl stop database -db orcl
[grid@rac1 ~]$ srvctl start database -db orcl -o mount

SQL> conn /as sysdba
接続されました。
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB                            MOUNTED
SQL> alter session set container = pdb;

セッションが変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3 <---★

まとめ

▽表領域のflashback on/off実施の条件
フラッシュバックデータベースがONとなっている必要がある。

▽CDB/PDBの停止等が必要か
表領域のFLASHBACK ONはインスタンスレベルでMOUNT状態にする必要がある。 ※OFFはOPEN状態で可

PDBレベルでのフラッシュバックデータベースを実行

概要

12.2から導入されたPDB レベルでフラッシュバックを実行する方法について説明します。

前提

・本手順はローカルUNDOであることを前提にしています。 ローカル/共有の確認および有効化は下記で可能です。

UNDOモードの確認(TRUE:ローカルUNDO FALSE:共有UNDO)

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';

PROPERTY_VALUE
--------------------
TRUE

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

フラッシュバックON

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

・フラッシュバック・データベース実行に必要な権限は以下のいずれかです。
- SYSDBA権限
- SYSBACKUP権限

使用コマンド

  1. PDB レベルでのリストアポイント作成
    --PDBから
    特定の PDB に接続し、CREATE RESTORE POINT コマンドを実行:
SQL> alter session set container=PDB1;  
SQL> create restore point TEST1;  
SQL> create restore point TEST2 guarantee flashback database;  

--CDBから
CDB に接続し、CREATE RESTORE POINT コマンドの FOR PLUGGABLE DATABASE 句を使用:

SQL> alter session set container=CDB$ROOT;  
SQL> create restore point TEST1 for pluggable database PDB1;  
SQL> create restore point TEST2 for pluggable database PDB1 guarantee flashback database;  

※本手順ではCDBから実行する手順となります。

--リストアポイントへのフラッシュバック手順(※PDB がローカルUNDO を使用している場合)

SQL> alter pluggable database PDB1 close;
SQL> flashback pluggable database PDB1 to restore point TEST1;
SQL> alter pluggable database PDB1 open resetlogs;

実行例

--リストアポイントの作成

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create restore point TEST1 for pluggable database PDB1;

Restore point created.

SQL> create restore point TEST2 for pluggable database PDB1 guarantee flashback database;

Restore point created.

--リストアポイントの情報の表示(※SQL*Plus または RMAN を使用)

SQL> SELECT NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

NAME                 TIME                                    SCN PDB GUA
-------------------- -------------------------------- ---------- --- ---
TEST2                19-NOV-20 03.13.01.000000000 PM     9457194 YES YES
TEST1                19-NOV-20 03.12.53.000000000 PM     9457178 YES NO

RMAN> list restore point all;

リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用している

SCN              RSP Time Type       Time     Name
---------------- -------- ---------- -------- ----
9457178                              20-11-19 TEST1
9457194                   GUARANTEED 20-11-19 TEST2

--意図したリストアポイントに戻るか否かの確認

SQL> show user
USER is "SCOTT"
SQL> select * from tab;

TNAME                TABTYPE                                  CLUSTERID
-------------------- --------------------------------------- ----------
DEPT                 TABLE
EMP                  TABLE
BONUS                TABLE
SALGRADE             TABLE
DUMMY                TABLE

SQL> create table flbtbl(col1 number, col2 varchar(10));

Table created.

SQL> insert into flbtbl values(10,'a');

1 row created.

SQL> insert into flbtbl values(10,'a');

insert into flbtbl values(20,'b');

SQL> insert into flbtbl values(30,'c');

1 row created.

SQL> insert into flbtbl values(30,'c');

1 row created.

SQL> insert into flbtbl values(40,'d');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from flbtbl;

      COL1 COL2
---------- ------------------------------
        10 a
        10 a
        30 c
        30 c
        40 d

--フラッシュバックを実行 a. PDB がローカルUNDO を使用している場合:

SQL> alter pluggable database PDB1 close;
SQL> flashback pluggable database PDB1 to restore point TEST1;
SQL> alter pluggable database PDB1 open resetlogs;
SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> flashback pluggable database PDB1 to restore point TEST1;
flashback pluggable database PDB1 to restore point TEST1
*
ERROR at line 1:
ORA-65025: Pluggable database  is not closed on all instances.

--Node2

  SQL> alter pluggable database PDB1 close;
 
  Pluggable database altered.

SQL> flashback pluggable database PDB1 to restore point TEST1;

Flashback complete.

SQL> alter pluggable database PDB1 open resetlogs;

Pluggable database altered.


SQL> conn scott/tiger@pdb1;
Connected.

SQL> select * from tab;

TNAME                TABTYPE                                  CLUSTERID
-------------------- --------------------------------------- ----------
DEPT                 TABLE
EMP                  TABLE
BONUS                TABLE
SALGRADE             TABLE
DUMMY                TABLE

Oracle RAC環境での oracle バイナリの権限(SGID oracle:asmadmin)

概要

ASM使用環境下でRMANでバックアップした場合などにバックアップセットのOSグループがasmadminグループとなります。
※oracleユーザーの所属するグループは、asmadminグループには属していない

# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54327(asmdba)
# id grid
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54330(racdba),54327(asmdba),54328(asmoper),54329(asmadmin)

原因

結論として、バックアップファイルのパーミッションが「oracle:asmadmin」となるのは"GI(ASM)環境"の仕様動作となります。

11.2 以降のRAC環境ででGrid InfrastructureとDatabaseを別々のOSユーザ(例:oracle/grid)でインストールしている場合、
relink処理直後のデータベースのoracleバイナリにはASMにアクセスするための権限が設定されていません。

DBCAでデータベースを作成した場合やsrvctlでデータベースを起動すると、
内部で上述のASMへのアクセス権限のあるグループで起動するようoracleバイナリの権限が設定されます(sビット)。
そのため起動にsrvctl使用していればrelinkが実行され、手動でもDBCAでもsビットが立つ動作です。

なお、create database文を直接実行してのデータベース作成直後は、追加で下記スクリプトを実行する必要がある。

  例
  ------------------------------
  # $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle
  ------------------------------
  ※上記の $GRID_HOME は Grid Infrastructure の $ORACLE_HOME
    $ORACLE_HOME はデータベースの $ORACLE_HOME を指しています

上記を実行することで次回relinkなどで権限が変更されるまでデータベースが ASM にアクセス可能な状態で起動される。

SGIDについて(Linux)

ファイルにSGIDを付与した場合、そのコマンドが所有グループの権限で実行される
Set Group ID
- 「SGID」とは「Set Group ID」の略。
- グループに対して設定される特殊パーミッション。
ポイント
- ファイルにSGIDが適応されていた場合、そのグループの権限でファイルが実行される
- ディレクトリにSGIDを指定すると、ディレクトリ内部で作成されたファイルは全てディレクトリのSGIDで指定したグループが割り振られる

動作確認

  • oracleユーザーでRMAN接続し、バックアップ
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on 火 112 10:46:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
ターゲット・データベース: ORCL (DBID=1587538681)に接続されました
RMAN> backup current controlfile format '/home/oracle/work/backup/%U';
backupを21-01-12で開始しています
リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
チャネル: ORA_DISK_1が割り当てられました
チャネルORA_DISK_1: SID=309 インスタンス=orcl1 デバイス・タイプ=DISK
チャネルORA_DISK_1: フル・データファイル・バックアップ・セットを開始しています
チャネルORA_DISK_1: バックアップ・セットにデータファイルを指定しています
現行の制御ファイルをバックアップ・セットに組み込んでいます
チャネルORA_DISK_1: ピース1 (21-01-12)を起動します
チャネルORA_DISK_1: ピース1 (21-01-12)が完了しました
ピース・ハンドル=/home/oracle/work/backup/03vjs2tp_1_1 タグ=TAG20210112T104720 コメント=NONE
チャネルORA_DISK_1: バックアップ・セットが完了しました。経過時間: 00:00:01
backupを21-01-12で終了しました
Control File and SPFILE Autobackupを21-01-05で開始しています
ピース・ハンドル=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-1587538681-20210112-00 コメント=NONE
Control File and SPFILE Autobackupを21-01-12で終了しました
RMAN> exit
Recovery Managerが完了しました。
  • バックアップファイルのパーミッション確認(oracle:asmadmin)
$ pwd
/home/oracle/work/backup
$ ls -la
合計 19328
drwxr-xr-x 2 oracle oinstall       26  112 10:47 .
drwxr-xr-x 3 oracle oinstall       20  112 10:46 ..
-rw-r----- 1 oracle asmadmin 19791872  112 10:47 03vjs2tp_1_1 <---★
  • ファイルを別ディレクトリにコピーし、再度パーミッション確認(oracle:oinstall)
$ ls -la
合計 19328
drwxr-xr-x 3 oracle oinstall       40  112 10:48 .
drwxr-xr-x 3 oracle oinstall       17  112 10:46 ..
-rw-r----- 1 oracle oinstall 19791872  112 10:48 03vjs2tp_1_1 <---★
drwxr-xr-x 2 oracle oinstall       26  112 10:47 backup
  • oracleバイナリから実行・出力されたファイルは以下のUID/GID
 $ ls -la $ORACLE_HOME/bin/oracle*
  -rwsr-s--x 1 oracle asmadmin 441253072 124 18:44 /u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle

oracleバイナリをasmadminグループの権限で実行しています。
※$ORACLE_HOME/bin/oracleのoracle binaryがasmadminグループに所属するため、oracle ユーザで取得したRMANバックアップファイルの所属グループがasmadminとなる。

データタイプANYDATAの参照方法

データタイプANYDATAの参照方法

概要

Oracle Databaseには複数のデータ型(VARCHAR2、NUMBER等々)のデータを格納することができるANYDATAという特殊なデータ型が存在しています。

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c 272 ANYDATAタイプ

ANYDATAタイプには、指定のタイプのインスタンスおよびそのタイプの説明が含まれています。つまり、ANYDATAは自己記述型です。ANYDATAは、データベースに永続的に格納できます。 

通常、テーブル設計・作成時に意図してこのデータ型を指定することはないと思いますが、SPA(SQL Performance Analyzer)などで利用するSTS(SQL Tuning Set)に格納されたバインド変数値で使用されていたりします。
そのため、バージョンアップ検証などでSPAを使用した際に確認対象のSQLで使用しているバインド変数値を確認したい場合などにはこのデータを参照するために特殊なアクセス・ファンクションを使う必要があります。

STS(SQL Tuning Set)とは

SPA(SQL Performance Analyzer)やアドバイザ等に対するINPUT情報として利用できます。
下記の情報を含むデータベース・オブジェクトです。

SQL文(SQLテキスト)
実行コンテキスト(スキーマ、アプリケーション・モジュール名、バインド変数など)
実行統計(実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など)
実行計画

このSTSの情報を各種ツールにかけて分析を実行します。

f:id:undercovergeek:20210109103403p:plain

ANYDATAの参照

STSに含めたバインド変数を使用したSQL

SQL_ID:1y431vxyc0xj7

variable var1 number;
variable var2 char(10);
variable var3 varchar2(10);
variable var4 varchar2(19);
execute :var1 := 1;
execute :var2 := 'a1';
execute :var3 := 'b1';
execute :var4 := '03-06-2010 18:00:00';
select * from tab1 
where col1 = :var1 and col2 = :var2 and col3= :var3
and col4 = to_date(:var4, 'DD-MM-YYYY HH24:MI:SS');

そのまま参照では見れない(「ANYDATA()」が返却される)

SQL> select SQL_ID,VALUE FROM ALL_SQLSET_BINDS WHERE SQL_ID='1y431vxyc0xj7';
SQL_ID                                  VALUE()
--------------------------------------- --------------------------------------------------
1y431vxyc0xj7                           ANYDATA()
1y431vxyc0xj7                           ANYDATA()
1y431vxyc0xj7                           ANYDATA()
1y431vxyc0xj7                           ANYDATA()

参照方法(fファンクションを使って参照)

GETTYPENAMEメンバー・ファンクションを使用してそれぞれキャストして返す(※キャラクタ型はそのままアクセスファンクション)

SQL> SELECT
  ANYDATA.GETTYPENAME(value) TYPE_NAME,
      CASE ANYDATA.GETTYPENAME(value)
        WHEN 'SYS.NUMBER'   THEN TO_CHAR(ANYDATA.ACCESSNUMBER(value))
        WHEN 'SYS.DATE'     THEN TO_CHAR(ANYDATA.ACCESSDATE(value))
        WHEN 'SYS.VARCHAR2' THEN ANYDATA.ACCESSVARCHAR2(value)
        WHEN 'SYS.CHAR'     THEN ANYDATA.ACCESSCHAR(value)
        ELSE NULL
      END AS DATA
FROM DBA_SQLSET_BINDS
WHERE SQL_ID = '1y431vxyc0xj7'
;
  2    3    4    5    6    7    8    9   10   11   12  
TYPE_NAME            DATA
-------------------- --------------------
SYS.NUMBER           1
SYS.CHAR             a1
SYS.VARCHAR2         b1
SYS.VARCHAR2         03-06-2010 18:00:00

バインド変数が確認できました。
今回参照した以外のデータ型も参考資料にあるアクセスファンクションで参照できます。

参考資料

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c
272 ANYDATAタイプ
https://docs.oracle.com/cd/F19136_01/arpls/ANYDATA-TYPE.html#GUID-08696B1A-5E92-45C6-89E9-00F934311797

スポンサーリンク