[Oracle]DBサーバー設定値確認(Linux/GI/ASM/Database)
Oracle Databaseサーバーの各設定値を確認するコマンドをまとめました。ちょっとずつ追加していく予定です。
Oracle19c をベースにしています。
コンテナ(PDB)単位に変更可能なものはCDBビューを使用して一括確認しています。項目名【PDB変更可】と記しています。
目次
ソフトウェアバージョン、エディション
SQL
select BANNER_FULL from v$version;
実行例
SQL> select BANNER_FULL from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
適用済みパッチ一覧
コマンド
su - oracle -c '$ORACLE_HOME/OPatch/opatch lspatches'
実行例
[root@host1 ~]# su - oracle -c '$ORACLE_HOME/OPatch/opatch lspatches'
30557433;Database Release Update : 19.6.0.0.200114 (30557433)
30489227;OCW RELEASE UPDATE 19.6.0.0.0 (30489227)
OPatch succeeded.
[root@host1 ~]#
制御ファイルのロケーション
SQLPlusコマンド
show parameter control_files
実行例
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/HANPEN/CONTROLFILE/curre
nt.263.1057722365
SQL>
文字コード【PDB変更可】
SQL
col PROPERTY_NAME format a30
col PROPERTY_VALUE format a20
select CON_ID,PROPERTY_NAME,PROPERTY_VALUE from CDB_PROPERTIES where PROPERTY_NAME LIKE 'NLS%CHARACTERSET' order by 1,2;
実行例
SQL> select CON_ID,PROPERTY_NAME,PROPERTY_VALUE from CDB_PROPERTIES where PROPERTY_NAME LIKE 'NLS%CHARACTERSET' order by 1,2;
CON_ID PROPERTY_NAME PROPERTY_VALUE
---------- ------------------------------ --------------------
1 NLS_CHARACTERSET AL32UTF8
1 NLS_NCHAR_CHARACTERSET AL16UTF16
3 NLS_CHARACTERSET AL32UTF8
3 NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
RMANバックアップ定義
RMANコマンド
rman target /
show all;
実行例
RMAN> show all;
リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
db_unique_name HANPENのデータベースにおけるRMAN構成パラメータ:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_hanpen1.f'; # default
RMAN>
データベースのデフォルト表領域、デフォルト一時表領域、デフォルト表領域タイプ【PDB変更可】
SQL
col PROPERTY_NAME format a30
col PROPERTY_VALUE format a20
select CON_ID,PROPERTY_NAME,PROPERTY_VALUE from CDB_PROPERTIES where PROPERTY_NAME in ('DEFAULT_TBS_TYPE','DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE') order by 1,2;
実行例
SQL> select CON_ID,PROPERTY_NAME,PROPERTY_VALUE from CDB_PROPERTIES where PROPERTY_NAME in ('DEFAULT_TBS_TYPE','DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE') order by 1,2;
CON_ID PROPERTY_NAME PROPERTY_VALUE
---------- ------------------------------ --------------------
1 DEFAULT_PERMANENT_TABLESPACE USERS
1 DEFAULT_TBS_TYPE SMALLFILE
1 DEFAULT_TEMP_TABLESPACE TEMP
3 DEFAULT_PERMANENT_TABLESPACE USERS
3 DEFAULT_TBS_TYPE SMALLFILE
3 DEFAULT_TEMP_TABLESPACE TEMP
6行が選択されました。
SQL>
ユーザー一覧【PDB変更可】
SQL
set linesize window
col CONNAME format a30
col USERNAME format a30
col DEFAULT_TABLESPACE format a30
col TEMPORARY_TABLESPACE format a30
col PROFILE format a30
select con_id_to_con_name(CON_ID) CONNAME,USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,COMMON,ORACLE_MAINTAINED from CDB_USERS order by CON_ID,USERNAME;
実行例
SQL> select con_id_to_con_name(CON_ID) CONNAME,USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,COMMON,ORACLE_MAINTAINED from CDB_USERS order by CON_ID,USERNAME;
CONNAME USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE COM O
------------------------------ ------------------------------ -------------------------------- ------------------------------ ------------------------------ ------------------------------ --- -
CDB$ROOT ANONYMOUS EXPIRED & LOCKED SYSAUX TEMP DEFAULT YES Y
CDB$ROOT APPQOSSYS LOCKED SYSAUX TEMP DEFAULT YES Y
CDB$ROOT AUDSYS LOCKED USERS TEMP DEFAULT YES Y
～中略～
PDBHANPEN01 WMSYS LOCKED SYSAUX TEMP DEFAULT YES Y
PDBHANPEN01 XDB LOCKED SYSAUX TEMP DEFAULT YES Y
PDBHANPEN01 XS$NULL EXPIRED & LOCKED SYSTEM TEMP DEFAULT YES Y
60行が選択されました。
SQL>
プロファイル一覧【PDB変更可】
SQL
set linesize window
col CONNAME format a30
col LIMIT format a30
col PROFILE format a30
select con_id_to_con_name(CON_ID) CONNAME,PROFILE,RESOURCE_TYPE,RESOURCE_NAME,LIMIT,COMMON from CDB_PROFILES order by CON_ID,PROFILE,RESOURCE_TYPE,RESOURCE_NAME;
実行例
SQL> select con_id_to_con_name(CON_ID) CONNAME,PROFILE,RESOURCE_TYPE,RESOURCE_NAME,LIMIT,COMMON from CDB_PROFILES order by CON_ID,PROFILE,RESOURCE_TYPE,RESOURCE_NAME;
CONNAME PROFILE RESOURCE RESOURCE_NAME LIMIT COM
------------------------------ ------------------------------ -------- -------------------------------- ------------------------------ ---
CDB$ROOT DEFAULT KERNEL COMPOSITE_LIMIT UNLIMITED NO
CDB$ROOT DEFAULT KERNEL CONNECT_TIME UNLIMITED NO
CDB$ROOT DEFAULT KERNEL CPU_PER_CALL UNLIMITED NO
CDB$ROOT DEFAULT KERNEL CPU_PER_SESSION UNLIMITED NO
～中略～
PDBHANPEN01 ORA_STIG_PROFILE PASSWORD PASSWORD_REUSE_MAX 10 NO
PDBHANPEN01 ORA_STIG_PROFILE PASSWORD PASSWORD_REUSE_TIME 365 NO
PDBHANPEN01 ORA_STIG_PROFILE PASSWORD PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION NO
68行が選択されました。
SQL>
ロール一覧【PDB変更可】
SQL ※ユーザー作成のロールのみに絞る
set linesize window
col CONNAME format a30
col ROLE format a30
select con_id_to_con_name(CON_ID) CONNAME,ROLE,COMMON,ORACLE_MAINTAINED from CDB_ROLES where ORACLE_MAINTAINED = 'N' order by CON_ID,ROLE;
実行例
SQL> select con_id_to_con_name(CON_ID) CONNAME,ROLE,COMMON,ORACLE_MAINTAINED from CDB_ROLES where ORACLE_MAINTAINED = 'N' order by CON_ID,ROLE;
CONNAME ROLE COM O
------------------------------ ------------------------------ --- -
CDB$ROOT C##APPS YES N
PDBHANPEN01 C##APPS YES N
PDBHANPEN01 REGI NO N
PDBHANPEN01 THE7 NO N
SQL>
ディレクトリオブジェクト一覧【PDB変更可】
SQL
set linesize window
col CONNAME format a30
col OWNER format a30
col DIRECTORY_NAME format a30
col DIRECTORY_PATH format a100
select con_id_to_con_name(CON_ID) CONNAME,OWNER,DIRECTORY_NAME,DIRECTORY_PATH from CDB_DIRECTORIES order by CON_ID,OWNER,DIRECTORY_NAME;
実行例
SQL> select con_id_to_con_name(CON_ID) CONNAME,OWNER,DIRECTORY_NAME,DIRECTORY_PATH from CDB_DIRECTORIES order by CON_ID,OWNER,DIRECTORY_NAME;
CONNAME OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------
CDB$ROOT SYS DATA_PUMP_DIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/
CDB$ROOT SYS DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
CDB$ROOT SYS DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs
CDB$ROOT SYS JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/19.0.0/dbhome_1/javavm/admin/
～中略～
PDBHANPEN01 SYS SUBDIR /home/oracle/db-sample-schemas-19.2/order_entry//2002/Sep
PDBHANPEN01 SYS XMLDIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/xml
PDBHANPEN01 SYS XSDDIR /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/xml/schema
31行が選択されました。
SQL>
自動メンテナンスタスク【PDB変更可】
SQL
set linesize window
col CONNAME format a30
select con_id_to_con_name(CON_ID) CONNAME,CLIENT_NAME,STATUS from CDB_AUTOTASK_CLIENT order by con_id,CLIENT_NAME;
実行例
SQL> select con_id_to_con_name(CON_ID) CONNAME,CLIENT_NAME,STATUS from CDB_AUTOTASK_CLIENT order by con_id,CLIENT_NAME;
CONNAME CLIENT_NAME STATUS
------------------------------ ---------------------------------------------------------------- --------
CDB$ROOT auto optimizer stats collection ENABLED
CDB$ROOT auto space advisor ENABLED
CDB$ROOT sql tuning advisor ENABLED
PDBHANPEN01 auto optimizer stats collection ENABLED
PDBHANPEN01 auto space advisor ENABLED
PDBHANPEN01 sql tuning advisor ENABLED
6行が選択されました。
SQL>
REDOログファイル一覧
SQL
set linesize window
col member for a60
set num 10
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
select f.GROUP#,f.TYPE,
case f.TYPE
when 'ONLINE' THEN l.THREAD#
when 'STANDBY' THEN s.THREAD#
END AS THREAD#
,f.MEMBER,
case f.TYPE
when 'ONLINE' THEN l.BYTES/1024/1024
when 'STANDBY' THEN s.BYTES/1024/1024
END AS MB
,f.STATUS FILE_STATUS,
case f.TYPE
when 'ONLINE' THEN l.STATUS
when 'STANDBY' THEN s.STATUS
END AS LOG_STATUS
,f.IS_RECOVERY_DEST_FILE
from v$logfile f
left outer join v$log l on f.GROUP#=l.GROUP#
left outer join v$standby_log s on f.GROUP#=s.GROUP#
order by 1,2,3,4
;
実行例
SQL> select f.GROUP#,f.TYPE,
2 case f.TYPE
3 when 'ONLINE' THEN l.THREAD#
4 when 'STANDBY' THEN s.THREAD#
5 END AS THREAD#
6 ,f.MEMBER,
7 case f.TYPE
8 when 'ONLINE' THEN l.BYTES/1024/1024
9 when 'STANDBY' THEN s.BYTES/1024/1024
10 END AS MB
11 ,f.STATUS
12 ,f.IS_RECOVERY_DEST_FILE
13 from v$logfile f
14 left outer join v$log l on f.GROUP#=l.GROUP#
15 left outer join v$standby_log s on f.GROUP#=s.GROUP#
16 order by 1,2,3,4
17 ;
GROUP# TYPE THREAD# MEMBER MB FILE_ST LOG_STATUS IS_
---------- ------- ---------- ------------------------------------------------------------ ---------- ------- ---------------- ---
1 ONLINE /opt/oracle/oradata/ORADG/redo01.log DELETED NO
2 ONLINE /opt/oracle/oradata/ORADG/redo02.log DELETED NO
3 ONLINE /opt/oracle/oradata/ORADG/redo03.log DELETED NO
11 STANDBY 1 /opt/oracle/oradata/physFS/standby11.log 200 UNASSIGNED NO
12 STANDBY 1 /opt/oracle/oradata/physFS/standby12.log 200 ACTIVE NO
13 STANDBY 1 /opt/oracle/oradata/physFS/standby13.log 200 UNASSIGNED NO
14 STANDBY 1 /opt/oracle/oradata/physFS/standby14.log 200 UNASSIGNED NO
SQL>
