ORA-1555 の再現ケース
事前準備
自動拡張 OFF の10MB のUNDO表領域を予め作成しておく
データ作成
SQL> drop table t1 purge; SQL> create table t1 (c1 number(10), c2 char(500)); SQL> insert into t1 select level, 'AAA' from dual connect by level <=10000; SQL> commit; SQL> select segment_name, bytes/1024/1024 MB from user_segments; SEGMENT_NA MB ---------- ---------- T1 6
1つめのセッションから 時間のかかる select 発行
14:57:43 SQL> set time on timing on 14:57:44 SQL> select 14:57:44 2 /*+ LEADING(a b) USE_NL(b) */ 14:57:44 3 a.c1, a.c2 14:57:44 4 from t1 a, t1 b 14:57:44 5 order by a.c1, b.c1 14:57:44 6 ;
select 実行中に2つめのセッションからT1 を更新
SQL> update t1 set c2 = 'XXX'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; update t1 set c2 = 'YYY'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; update t1 set c2 = 'ZZZ'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; 10000 rows updated. Elapsed: 00:00:00.74 14:57:56 SQL> STATUS MB --------- ---------- UNEXPIRED 2.375 EXPIRED .125 ACTIVE 6.5 Elapsed: 00:00:00.00 14:57:56 SQL> Commit complete. Elapsed: 00:00:00.00 14:57:56 SQL> STATUS MB --------- ---------- UNEXPIRED 8.6875 EXPIRED .125 ACTIVE .1875 Elapsed: 00:00:00.00 14:57:56 SQL> 10000 rows updated. Elapsed: 00:00:00.87 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 2.375 EXPIRED .0625 ACTIVE 6.5625 Elapsed: 00:00:00.00 14:57:57 SQL> Commit complete. Elapsed: 00:00:00.01 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 8.75 EXPIRED .0625 ACTIVE .1875 Elapsed: 00:00:00.00 14:57:57 SQL> 10000 rows updated. Elapsed: 00:00:00.50 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 2.4375 EXPIRED .0625 ACTIVE 6.5 Elapsed: 00:00:00.00 14:57:57 SQL> Commit complete. Elapsed: 00:00:00.01 14:57:57 SQL>
1つめのセッションにエラーが返る
14:57:43 SQL> set time on timing on 14:57:44 SQL> select 14:57:44 2 /*+ LEADING(a b) USE_NL(b) */ 14:57:44 3 a.c1, a.c2 14:57:44 4 from t1 a, t1 b 14:57:44 5 order by a.c1, b.c1 14:57:44 6 ; from t1 a, t1 b * ERROR at line 4: ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2_2715918981$" too small Elapsed: 00:00:13.03 14:57:57 SQL> 14:57:57 SQL>
参考
Atcoderを解く環境のためのDockerfile
Atcoderを解く環境のためのDockerfile。 コードは VS Code の remote container を使って書く。
参考
- Windows 10 上で atcoder-cli を online-judge-tools と連携させて使う:導入からテストと提出までの基本的操作 - はむ吉(のんびり)の練習ノート
- atcoder-cli インストールガイド | わたしろぐ
- そもそもnpmからわからない
- Linux Prerequisites for Visual Studio Code Remote Development
- nvmとは何か?Node.jsのインストールと最新の安定版にアップデートする方法|バージョン指定と変更(npm)
- リリース一覧 | Node.js
- Node.js Packages for Oracle Linux | Integrated Cloud. Applications & Platform Services.
Dockerコンテナにssh接続
とりあえずメモ。ssh ノンパスを設定したコンテナへの接続。
ホストのssh鍵作成
ssh-keygen
コンテナのホスト鍵作成
ssh-keygen -t rsa -N "" -f ./ssh_host_rsa_key ssh-keygen -t dsa -N "" -f ./ssh_host_dsa_key ssh-keygen -t ecdsa -N "" -f ./ssh_host_ecdsa_key ssh-keygen -t ed25519 -N "" -f ./ssh_host_ed25519_key tar zvcf ssh_host_keys.tar.gz ssh* ls -l ssh*
Dockerfile
FROM oraclelinux:7 RUN yum install -y openssh-server RUN echo "#PermitRootLogin yes" >> /etc/ssh/sshd_config #### root のログインを許可 RUN mkdir -p /root/.ssh/ RUN echo "ssh-rsa ********" >> /root/.ssh/authorized_keys #### ホストの公開鍵 COPY ./ssh_host_keys.tar.gz /etc/ssh/ RUN tar zxf /etc/ssh/ssh_host_keys.tar.gz -C /etc/ssh/ #### 事前にコンテナのホスト鍵を作成しておき展開 CMD /usr/sbin/sshd && tail -f /dev/null
Build
docker build ./ --tag ol7ssh
RUN
docker run -d --rm -p 127.0.0.1:10022:22 ol7ssh
ssh接続
ssh root@127.0.0.1 -p 10022
参考
sshd の常駐方法については、こちらを参考にしました。
Oracle Linux にpyenv をいれるDockerfile
とりあえずメモ。
FROM oraclelinux:7 RUN yum install -y gcc zlib-devel bzip2 bzip2-devel readline readline-devel sqlite sqlite-devel openssl openssl-devel git libffi-devel RUN git clone https://github.com/pyenv/pyenv.git ~/.pyenv RUN echo 'export PYENV_ROOT="$HOME/.pyenv"' >> ~/.bashrc RUN echo 'export PATH="$PYENV_ROOT/bin:$PATH"' >> ~/.bashrc RUN echo 'eval "$(pyenv init --path)"' >> ~/.bashrc RUN echo 'eval "$(pyenv init -)"' >> ~/.bashrc RUN $HOME/.pyenv/bin/pyenv install pypy3.8-7.3.7 RUN $HOME/.pyenv/bin/pyenv global pypy3.8-7.3.7 RUN $HOME/.pyenv/bin/pyenv rehash
Oracle SQLモニターの確認方法
対象SQLの確認
SQL> select sid, sql_id, status, to_char(sql_exec_start, 'yyyy/mm/dd hh24:mi:ss') start_time from v$sql_monitor order by status, sql_exec_start ; SID SQL_ID STATUS START_TIME ---------- ------------- ------------------- ------------------- 150 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 148 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 404 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 393 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 276 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 28 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10 30 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02 391 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02 275 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02 152 g3bca740t9wjp EXECUTING 2022/02/16 15:27:18 10 rows selected.
レポートの表示
SQL> set long 10000 line 400 pages 1000 tab off col data for a400 select dbms_sqltune.report_sql_monitor( session_id=> 152, sql_id=> 'g3bca740t9wjp', type=>'TEXT' ) data from dual; DATA ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL Monitoring Report SQL Text ------------------------------ select * from t01, t02 order by 1,2,3 Global Information ------------------------------ Status : EXECUTING Instance ID : 1 Session : USER01 (152:63240) SQL ID : g3bca740t9wjp SQL Execution ID : 16777220 Execution Started : 02/16/2022 15:41:12 First Refresh Time : 02/16/2022 15:41:16 Last Refresh Time : 02/16/2022 15:41:24 Duration : 13s Module/Action : SQL*Plus/- Service : pdb1 Program : sqlplus@db01 (TNS V1-V3) Global Stats ========================================================= | Elapsed | Cpu | IO | Buffer | Write | Write | | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | ========================================================= | 12 | 7.21 | 4.50 | 38 | 3076 | 617MB | ========================================================= SQL Plan Monitoring Details (Plan Hash Value=1870084974) ========================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | | (%) | (# samples) | ========================================================================================================================================================================== | -> 0 | SELECT STATEMENT | | | | 9 | +4 | 1 | 0 | | | . | . | | | | -> 1 | SORT ORDER BY | | 1G | 6M | 11 | +2 | 1 | 0 | 3076 | 617MB | 745KB | 618MB | 90.91 | Cpu (2) | | | | | | | | | | | | | | | | direct path write temp (8) | | -> 2 | MERGE JOIN CARTESIAN | | 1G | 548K | 9 | +4 | 1 | 27M | | | . | . | | | | -> 3 | TABLE ACCESS FULL | T02 | 100K | 69 | 9 | +4 | 1 | 2656 | | | . | . | | | | -> 4 | BUFFER SORT | | 10000 | 6M | 9 | +4 | 2656 | 27M | | | 424KB | . | 9.09 | Cpu (1) | | 5 | TABLE ACCESS FULL | T01 | 10000 | 5 | 1 | +4 | 1 | 10000 | | | . | . | | | ==========================================================================================================================================================================
OCICLI コマンドとjq コマンドのメモ
目次
コンパートメントID取得
.name == の条件は必要に応じて変更。
$ oci iam compartment list | jq -r '.data | map(select(.name=="poul"))[].id '
コンピュートの表示名、ocid取得
$ oci compute instance list --compartment-id=$comp_id | jq -r '.data[] | {name:."display-name",id:."id"}' { "name": "db01", "id": "ocid1.instance.oc1.ap-tokyo-1.xxx" } $ oci compute instance list --compartment-id=$comp_id | jq -r '.data | map(select(."display-name"=="db01"))[] | .id' ocid1.instance.oc1.ap-tokyo-1.xxx
コンピュート起動
$ oci compute instance action --instance-id=$inst_id --action start
コンピュート停止
$ oci compute instance action --instance-id=$inst_id --action stop
OCICLI on docker container
目次
- 目次
- やること
- Oracle Linux のイメージをpull
- コンテナ起動
- Python3導入
- OCICLI インストール
- OCIコマンドの確認
- OCIコマンドの設定
- 公開鍵の確認
- ユーザーにAPIキーの設定
- 動作確認
- dockerイメージのコミット
- 参考
やること
OCICLI をDocker container上にインストールしたいと思います。
Oracle Linux のイメージをpull
$ docker pull oraclelinux:7-slim 7-slim: Pulling from library/oraclelinux 89ad662320ef: Pull complete Digest: sha256:68d5af67eaa92bb7346d6a7626761fc30a6d0934abbb32ac2af71d5d74b3023b Status: Downloaded newer image for oraclelinux:7-slim docker.io/library/oraclelinux:7-slim $ docker image list REPOSITORY TAG IMAGE ID CREATED SIZE docker/getting-started latest adfdb308d623 4 days ago 27.4MB oraclelinux 7-slim 1aadc5c6dfe3 4 weeks ago 162MB
コンテナ起動
$ docker run -it oraclelinux:7-slim bash-4.2#
Python3導入
bash-4.2# yum install python3
OCICLI インストール
bash-4.2# bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 16641 100 16641 0 0 73865 0 --:--:-- --:--:-- --:--:-- 73960 ****************************************************************************** You have started the OCI CLI Installer in interactive mode. If you do not wish to run this in interactive mode, please include the --accept-all-defaults option. If you have the script locally and would like to know more about input options for this script, then you can run: ./install.sh -h If you would like to know more about input options for this script, refer to: https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst ****************************************************************************** Downloading Oracle Cloud Infrastructure CLI install script from https://raw.githubusercontent.com/oracle/oci-cli/v3.2.1/scripts/install/install.py to /tmp/oci_cli_install_tmp_Q888. ######################################################################## 100.0% Running install script. python3 /tmp/oci_cli_install_tmp_Q888 -- Verifying Python version. -- Python version 3.6.8 okay. ===> In what directory would you like to place the install? (leave blank to use '/root/lib/oracle-cli'): -- Creating directory '/root/lib/oracle-cli'. -- We will install at '/root/lib/oracle-cli'. ===> In what directory would you like to place the 'oci' executable? (leave blank to use '/root/bin'): -- Creating directory '/root/bin'. -- The executable will be in '/root/bin'. ===> In what directory would you like to place the OCI scripts? (leave blank to use '/root/bin/oci-cli-scripts'): -- Creating directory '/root/bin/oci-cli-scripts'. -- The scripts will be in '/root/bin/oci-cli-scripts'. ===> Currently supported optional packages are: ['db (will install cx_Oracle)'] What optional CLI packages would you like to be installed (comma separated names; press enter if you don't need any optional packages)?: -- The optional packages installed will be ''. -- Trying to use python3 venv. -- Executing: ['/usr/bin/python3', '-m', 'venv', '/root/lib/oracle-cli'] -- Executing: ['/root/lib/oracle-cli/bin/pip', 'install', '--upgrade', 'pip'] Collecting pip Downloading https://files.pythonhosted.org/packages/a4/6d/6463d49a933f547439d6b5b98b46af8742cc03ae83543e4d7688c2420f8b/pip-21.3.1-py3-none-any.whl (1.7MB) 100% |████████████████████████████████| 1.7MB 1.1MB/s Installing collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3 Successfully installed pip-21.3.1 You are using pip version 21.3.1, however version 22.0.3 is available. You should consider upgrading via the 'pip install --upgrade pip' command. -- Executing: ['/root/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpgc3ok70p', 'wheel', '--upgrade'] Collecting wheel Downloading wheel-0.37.1-py2.py3-none-any.whl (35 kB) Installing collected packages: wheel Successfully installed wheel-0.37.1 -- Executing: ['/root/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpgc3ok70p', 'oci_cli', '--upgrade'] Collecting oci_cli Downloading oci_cli-3.5.0-py3-none-any.whl (24.3 MB) |████████████████████████████████| 24.3 MB 12.4 MB/s Collecting cryptography<=3.4.7,>=3.2.1 Downloading cryptography-3.4.7-cp36-abi3-manylinux2014_aarch64.whl (3.1 MB) |████████████████████████████████| 3.1 MB 12.1 MB/s Collecting python-dateutil<3.0.0,>=2.5.3 Downloading python_dateutil-2.8.2-py2.py3-none-any.whl (247 kB) |████████████████████████████████| 247 kB 9.6 MB/s Collecting click==7.1.2 Downloading click-7.1.2-py2.py3-none-any.whl (82 kB) |████████████████████████████████| 82 kB 3.4 MB/s Collecting pyOpenSSL==19.1.0 Downloading pyOpenSSL-19.1.0-py2.py3-none-any.whl (53 kB) |████████████████████████████████| 53 kB 5.4 MB/s Collecting pytz>=2016.10 Downloading pytz-2021.3-py2.py3-none-any.whl (503 kB) |████████████████████████████████| 503 kB 11.3 MB/s Collecting jmespath==0.10.0 Downloading jmespath-0.10.0-py2.py3-none-any.whl (24 kB) Collecting oci==2.56.0 Downloading oci-2.56.0-py2.py3-none-any.whl (12.5 MB) |████████████████████████████████| 12.5 MB 10.6 MB/s Collecting arrow>=1.0.0 Downloading arrow-1.2.2-py3-none-any.whl (64 kB) |████████████████████████████████| 64 kB 6.0 MB/s Collecting terminaltables==3.1.0 Downloading terminaltables-3.1.0.tar.gz (12 kB) Preparing metadata (setup.py) ... done Collecting certifi Downloading certifi-2021.10.8-py2.py3-none-any.whl (149 kB) |████████████████████████████████| 149 kB 11.2 MB/s Collecting six>=1.15.0 Downloading six-1.16.0-py2.py3-none-any.whl (11 kB) Collecting PyYAML<6,>=5.4 Downloading PyYAML-5.4.1-cp36-cp36m-manylinux2014_aarch64.whl (726 kB) |████████████████████████████████| 726 kB 11.2 MB/s Collecting circuitbreaker<2.0.0,>=1.3.1 Downloading circuitbreaker-1.3.2.tar.gz (7.9 kB) Preparing metadata (setup.py) ... done Collecting typing-extensions Downloading typing_extensions-4.1.1-py3-none-any.whl (26 kB) Collecting cffi>=1.12 Downloading cffi-1.15.0-cp36-cp36m-manylinux_2_17_aarch64.manylinux2014_aarch64.whl (433 kB) |████████████████████████████████| 433 kB 12.3 MB/s Collecting pycparser Downloading pycparser-2.21-py2.py3-none-any.whl (118 kB) |████████████████████████████████| 118 kB 12.8 MB/s Building wheels for collected packages: terminaltables, circuitbreaker Building wheel for terminaltables (setup.py) ... done Created wheel for terminaltables: filename=terminaltables-3.1.0-py3-none-any.whl size=15355 sha256=05e9fb3f5a78ad9460b858974cb1968899d812cde2d8e443003fe1b8d540423c Stored in directory: /tmp/tmpgc3ok70p/wheels/86/1b/58/c23af2fe683acd8edc15d5a1268f0242be1ff2cf827fe34737 Building wheel for circuitbreaker (setup.py) ... done Created wheel for circuitbreaker: filename=circuitbreaker-1.3.2-py3-none-any.whl size=6017 sha256=2059f5037fbaa29a7df94a3090622cf78e599b8e2f721098af74247526499b87 Stored in directory: /tmp/tmpgc3ok70p/wheels/2b/54/c2/70629ee4335118768dceec192e138df3636ecf02973fa2c1f0 Successfully built terminaltables circuitbreaker Installing collected packages: pycparser, cffi, six, cryptography, typing-extensions, pytz, python-dateutil, pyOpenSSL, circuitbreaker, certifi, terminaltables, PyYAML, oci, jmespath, click, arrow, oci-cli Successfully installed PyYAML-5.4.1 arrow-1.2.2 certifi-2021.10.8 cffi-1.15.0 circuitbreaker-1.3.2 click-7.1.2 cryptography-3.4.7 jmespath-0.10.0 oci-2.56.0 oci-cli-3.5.0 pyOpenSSL-19.1.0 pycparser-2.21 python-dateutil-2.8.2 pytz-2021.3 six-1.16.0 terminaltables-3.1.0 typing-extensions-4.1.1 ===> Modify profile to update your $PATH and enable shell/tab completion now? (Y/n): Y ===> Could not automatically find a suitable file to use. Create /root/.bashrc now? (Y/n): Y -- Backed up '/root/.bashrc' to '/root/.bashrc.backup' -- Tab completion set up complete. -- If tab completion is not activated, verify that '/root/.bashrc' is sourced by your shell. -- -- ** Run `exec -l $SHELL` to restart your shell. ** -- -- Installation successful. -- Run the CLI with /root/bin/oci --help
OCIコマンドの確認
bash-4.2# bash bash-4.2# oci -v 3.5.0
OCIコマンドの設定
★の箇所で値を入力します。
bash-4.2# oci setup config This command provides a walkthrough of creating a valid CLI config file. The following links explain where to find the information required by this script: User API Signing Key, OCID and Tenancy OCID: https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other Region: https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm General config documentation: https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm Enter a location for your config [/root/.oci/config]: Enter a user OCID: ocid1.user.oc1..**** ---- ★ Enter a tenancy OCID: ocid1.tenancy.oc1..**** ---- ★ Enter a region by index or name(e.g. 1: af-johannesburg-1, 2: ap-chiyoda-1, 3: ap-chuncheon-1, 4: ap-dcc-canberra-1, 5: ap-hyderabad-1, 6: ap-ibaraki-1, 7: ap-melbourne-1, 8: ap-mumbai-1, 9: ap-osaka-1, 10: ap-seoul-1, 11: ap-singapore-1, 12: ap-sydney-1, 13: ap-tokyo-1, 14: ca-montreal-1, 15: ca-toronto-1, 16: eu-amsterdam-1, 17: eu-frankfurt-1, 18: eu-marseille-1, 19: eu-milan-1, 20: eu-stockholm-1, 21: eu-zurich-1, 22: il-jerusalem-1, 23: me-abudhabi-1, 24: me-dcc-muscat-1, 25: me-dubai-1, 26: me-jeddah-1, 27: sa-santiago-1, 28: sa-saopaulo-1, 29: sa-vinhedo-1, 30: uk-cardiff-1, 31: uk-gov-cardiff-1, 32: uk-gov-london-1, 33: uk-london-1, 34: us-ashburn-1, 35: us-gov-ashburn-1, 36: us-gov-chicago-1, 37: us-gov-phoenix-1, 38: us-langley-1, 39: us-luke-1, 40: us-phoenix-1, 41: us-sanjose-1): 13 ---- ★ Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: Y Enter a directory for your keys to be created [/root/.oci]: Enter a name for your key [oci_api_key]: Public key written to: /root/.oci/oci_api_key_public.pem Enter a passphrase for your private key (empty for no passphrase): Private key written to: /root/.oci/oci_api_key.pem Fingerprint: 8d:e6:7e:cf:15:ec:b4:9a:5d:c8:d9:b9:5b:dc:03:53 Config written to /root/.oci/config If you haven't already uploaded your API Signing public key through the console, follow the instructions on the page linked below in the section 'How to upload the public key': https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2
user/tenancy OCIDの確認
遷移
user OCIDコピー
tenancy OCIDコピー
公開鍵の確認
bash-4.2# cat /root/.oci/oci_api_key_public.pem -----BEGIN PUBLIC KEY----- **************************** **************************** **************************** -----END PUBLIC KEY-----
ユーザーにAPIキーの設定
「プロファイル」-「ユーザー設定」から左タブの「APIキー」を選択
Paste pub key を選択し、確認した公開鍵を貼り付ける。Add -> Close。
鍵が追加されフィンガープリントが表示されていることを確認
動作確認
bash-4.2# oci iam region list { "data": [ { "key": "AMS", "name": "eu-amsterdam-1" }, { "key": "ARN", "name": "eu-stockholm-1" ・・・
dockerイメージのコミット
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES cea66ba5dfba oraclelinux:7-slim "/bin/bash" 29 minutes ago Up 29 minutes bold_fermi $ docker commit cea66ba5dfba ocicli $ docker images REPOSITORY TAG IMAGE ID CREATED SIZE ocicli latest 8ff29331de26 16 seconds ago 1.09GB docker/getting-started latest adfdb308d623 4 days ago 27.4MB oraclelinux 7-slim 1aadc5c6dfe3 4 weeks ago 162MB